Syntax
LOOKUP(lookup_value, lookup_vector, [result_vector])
| Parameter | Description |
|---|---|
| lookup_value | Parameter of the LOOKUP function. |
| lookup_vector | Parameter of the LOOKUP function. |
| [result_vector] | (Optional.) Parameter of the LOOKUP function. |
Examples
Grade lookup
Formula
=LOOKUP(85, {0,60,70,80,90}, {"F","D","C","B","A"})
Returns "B" — finds the largest value in the first array that is ≤ 85 (which is 80), then returns the corresponding element from the second array.
Price tier
Formula
=LOOKUP(A1, B1:B10, C1:C10)
Looks up A1 in the sorted range B1:B10 and returns the matching value from C1:C10. B1:B10 must be sorted ascending.
Last non-empty value
Formula
=LOOKUP(2, 1/(A1:A100<>""), A1:A100)
A classic trick: finds the last non-empty cell in A1:A100 by exploiting LOOKUP's behavior of finding the largest match.
Common Errors
#N/A
Occurs when the lookup value is smaller than the smallest value in the lookup range. No match can be found.
#REF!
Occurs when the result vector is a different size than the lookup vector.
Tips
Data must be sorted
LOOKUP always does an approximate match on sorted ascending data. If your data isn't sorted or you need exact match, use VLOOKUP with FALSE or XLOOKUP.
Last-value trick
=LOOKUP(2,1/(range<>""),range) is a popular formula to find the last non-empty cell. It works because 1/TRUE=1 and LOOKUP finds the last 1.
Prefer XLOOKUP or VLOOKUP
LOOKUP is a legacy function. XLOOKUP (or VLOOKUP with exact match) is more flexible, doesn't require sorted data, and handles errors better.
Try LOOKUP in Viztab
Import your data and use LOOKUP with 370+ other formulas. No signup required.
Open Viztab