Lookup
LOOKUP
Definition
LOOKUP searches a sorted row or column for a value and returns a result from a matching position.
Syntax
=LOOKUP(lookup_value, lookup_vector, result_vector)
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The value to find. LOOKUP takes the largest key that is not above this value. |
lookup_vector |
A single row or column of keys in ascending order. |
result_vector |
The row or column of values to return from the matching position. |
Worked example
A fare sheet stores fare bands in A2:A4 and labels in B2:B4.
| A: Band | B: Label |
|---|---|
| 10 | x |
| 20 | y |
| 30 | z |
Formula: =LOOKUP(25,A2:A4,B2:B4)
Result: y. 25 is at least 20 and below 30, so LOOKUP takes the 20 row.
Practice
Range A2:A4 holds 1, 5, and 10. Range B2:B4 holds a, b, and c. In C2, write a formula that looks up 6 in A2:A4 and returns the matching value from B2:B4.
- In C2, write a LOOKUP formula that returns the value in B2:B4 for 6 from A2:A4.
- C2 should show the value for 6.
Step 1 of 2
Enter =LOOKUP(6,A2:A4,B2:B4) in C2.
The largest value at or below 6 is 5, which is b.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses LOOKUP.
C2 now shows b.
Related functions
VLOOKUP can require an exact match when you pass FALSE. INDEX can return a cell once MATCH finds a position.
Common mistake
Using LOOKUP on unsorted keys: it is an approximate search, so a list that is not in ascending order can return the wrong pair.