LOOKUP Function
Finds a value in one row or column and returns the matching item from another.
Category
Lookup & Reference
Available in
All versions, Microsoft 365
Returns
A value from the result range
Overview
LOOKUP is Excel's oldest lookup function. It always does an approximate match, so the lookup range must be sorted in ascending order.
Today, XLOOKUP is a better choice, but LOOKUP still has a famous trick: finding the last value in a column.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| lookup_value | Yes | The value to search for. |
| lookup_vector | Yes | One row or column to search, sorted ascending. |
| result_vector | Optional | One row or column of the same size to return from. |
Examples
Example 1 — Grade bands
Score thresholds are sorted in column A; grades in column B.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Min | Grade | Score | Grade |
| 2 | 0 | F | 78 | C |
| 3 | 60 | D | ||
| 4 | 70 | C | ||
| 5 | 80 | B |
78 is not found, so LOOKUP falls back to the largest value below it — 70 — and returns C.
Example 2 — The last value in a column
This classic trick returns the last non-empty value in column B.
1/(B:B<>"") gives 1 for filled cells and errors for blanks. LOOKUP can't find 2, so it settles on the last 1.
Common mistakes
- Unsorted data. LOOKUP silently returns wrong answers if lookup_vector isn't sorted.
- No exact match option. If you need an exact match, use XLOOKUP or INDEX/MATCH.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.