Excel Core Book
Get our FREE guide on Excel when you subscribe to our free newsletter
Finds a value either in single row or column or an array to return corresponding value.
Syntax of Excel LOOKUP Formula:
=LOOKUP(for this value, in this range, [fetching value from this range])
=LOOKUP(for this value, in the first column of this range to return corresponding value)
LOOKUP function still exists in function directory to provide backward compatibility. Though it works but it comes with sever limitations and so users should use better lookup methods like VLOOKUP, HLOOKUP or INDEX+MATCH combo.
One important fact to remember is that data must be in ascending order to make the results better.
Another point to remember is that if LOOKUP function fails to get the exact lookup value, then it will fetch the largest value from the data that is approximately equal to or less than lookup value itself.
Consider the following data:
If we want to know the units of A4 then we can use following formula in cell B13:
=LOOKUP(A13,A2:A11,B2:B11) and it will return 87 which is wrong. Reason being the data is not in ascending order.
Removing the result and sorting the data and then running a lookup will render correct result.