The LOOKUP function
Looks up a value in one row or column and returns the value in the same position from another. It always does an approximate match, so the lookup range must be sorted from smallest to largest.
Syntax
LOOKUP(lookup_value, lookup_vector, [result_vector])- lookup_value
- The value to look for. LOOKUP finds the largest value that is less than or equal to it.
- lookup_vector
- The row or column to search, sorted A to Z or smallest to largest.
- result_vector (optional)
- The row or column to return from, the same size as lookup_vector. Left out, LOOKUP returns from lookup_vector.
Examples
D1
=LOOKUP(185,2A2:A6,3B2:B6)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Lowest score | Grade | B | |
| 2 | 0 | F | ||
| 3 | 60 | D | ||
| 4 | 70 | C | ||
| 5 | 80 | B | ||
| 6 | 90 | A |
In plain words: Find 185 in 2the lowest scores in A2:A6, or the closest one below it, and return 3the grade next to it in B2:B6.
D1=LOOKUP(60,A2:A6,B2:B6)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Lowest score | Grade | D | |
| 2 | 0 | F | ||
| 3 | 60 | D | ||
| 4 | 70 | C | ||
| 5 | 80 | B | ||
| 6 | 90 | A |
D1=LOOKUP(2,1/(B2:B6<>""),B2:B6)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Day | Sales | 140 | |
| 2 | Mon | 120 | ||
| 3 | Tue | 95 | ||
| 4 | Wed | 140 | ||
| 5 | Thu | |||
| 6 | Fri |
Questions
Why does LOOKUP return the wrong value?
The lookup range is not sorted from smallest to largest. LOOKUP always looks for the closest value below, so on unsorted data it stops in the wrong place. For an exact match, use XLOOKUP or VLOOKUP with FALSE.
How do I get the last value in a column?
=LOOKUP(2,1/(B2:B100<>""),B2:B100) returns the last filled cell. Filled cells become 1 and empty ones an error, and LOOKUP looking for 2 lands on the last 1.