LOOKUP
Looks up a value in a range and returns a corresponding value from another range.
LOOKUP(lookup_value, lookup_vector, [result_vector])Arguments
lookup_valueValue to findlookup_vectorRange to searchresult_vectorRange for result(optional)
=LOOKUP(5,A1:A10,B1:B10)Finds 5 and returns from B
=LOOKUP(2,1/(A:A<>""),A:A)Gets last non-empty cell
=LOOKUP("Z",A:A,B:B)Finds closest less than Z
- •Data must be sorted ascending
- •Returns closest match less than or equal
- •XLOOKUP is more flexible
- •Forgetting LOOKUP requires the lookup vector to be sorted in ascending order - unlike VLOOKUP, it doesn't warn you, it just silently returns a wrong result
- •Using LOOKUP when VLOOKUP, INDEX/MATCH, or XLOOKUP would be clearer and safer, since LOOKUP's sorting requirement is an easy trap for anyone maintaining the sheet later
- •Confusing the vector form (two separate ranges) with the array form (one combined range) of LOOKUP, which have different syntax and easily get mixed up
Why does LOOKUP return the wrong value even though the data seems right?
LOOKUP requires the lookup column to be sorted ascending - if it isn't, LOOKUP won't error, it'll just silently return an incorrect match.
Should I use LOOKUP or VLOOKUP?
VLOOKUP is generally safer since it doesn't require sorted data and gives a clear error when it can't find a match. Use LOOKUP only if you specifically need its two-vector form for a simple, sorted lookup.
What's the difference between the vector and array forms of LOOKUP?
The vector form takes two separate ranges (one to search, one to return from). The array form takes a single range and searches its first row or column, returning from the last - it's less common and easier to misuse.
Need to translate a formula using LOOKUP?
Use our translator to convert your complete formula