Forum Discussion
Problem with Excel Formulae
Good morning
I regularly use VLOOKUP to search for a value in a table across a row, however, I now need to identify a value in a row and find the value in the column below it.
I have written this formula but it fails
IF(LEN(VLOOKUP(Sheet7!$A$4,'Delivery Plan'!$A$6:$AE$6,5,FALSE))=0,"",VLOOKUP('CMI 525'!$A$4,'Delivery Plan'!$A$6:$AE$6,5,FALSE))
The value in Sheet7 is Module# which it will reference in the Delivery Plan across the column headers Module1, Module2, etc and I want to identify the 5th value below this
Please could anyone advise where i am going wrong
- OK a few things:
a) it SOUNDS like you are looking across a row and want to return a value in a different row so that should be HLOOKUP instead of VLOOKUP (or better yet use XLOOKUP)
b) the range you give is a SINGLE row but when you use H or V LOOKUP you need a table so you can refer to the offset value. for example you give $A$6:$AE$6 and then want the 5th row but you only GAVE it 1 row (but in fact you used VLOOKUP so it is actually comparing $A$4 to ONLY $A$6 on that sheet and if that isn't what you want it can't find it)
c) do you realize that the first part checking if that value is blank uses Sheet7 but then the second part uses 'CMI525'? =HLOOKUP($A$4,'Delivery Plan'!$A$6:$AE$12,5,FALSE)You can try HLOOKUP.
Sheet "Delivery Plan":
Sheet "Sheet7":
