The Excel LOOKUP function is the simplest member of the lookup family: it searches a one-row or one-column range and returns the matching value from another. It is always an approximate match on sorted data — a real limitation — yet it survives in modern workbooks thanks to two tricks every Excel pro should know.
D2 falls into (lookup column sorted ascending):
Syntax (vector form)
| Argument | Description | |
|---|---|---|
lookup_value | Required | The value to search for. |
lookup_vector | Required | A single row or column, sorted ascending. |
result_vector | Optional | Same-size row or column to return values from. Omitted = return from lookup_vector itself. |
LOOKUP is always approximate. It finds the largest value ≤ lookup_value. There is no exact-match switch, and unsorted data returns wrong answers silently. (An "array form" also exists for backward compatibility; Microsoft recommends VLOOKUP/HLOOKUP instead.)
What it's good at: tiers and brackets
Because approximate match is the whole point, LOOKUP is naturally compact for commission tiers, tax brackets, and grading scales:
| A | B | D | E | ||
|---|---|---|---|---|---|
| 1 | Sales at least | Commission | Sales | Rate | |
| 2 | $0 | 2% | $83,500 | 6% | |
| 3 | $25,000 | 4% | |||
| 4 | $50,000 | 6% | |||
| 5 | $100,000 | 8% |
Compared to VLOOKUP’s approximate mode, there’s no column counting; compared to XLOOKUP, it’s shorter but far less safe. Remember: sorted ascending, always.
Try it: interactive LOOKUP demo
Drag the sales amount and watch LOOKUP slide between tiers.
The famous last-value tricks
LOOKUP’s quirk — approximate match returning the last value ≤ the target — powers two classics. To get the last number in a column (running totals, latest balance):
9.99E+307 is essentially the largest number Excel can store, so LOOKUP runs past every real value and lands on the final one. For the last non-blank cell of any type:
The test 1/(range<>"") builds an array of 1s (filled cells) and #DIV/0! errors (blanks). LOOKUP ignores the errors while hunting for 2, never finds it, and returns the last 1’s position — i.e., the last filled cell.
Errors & common pitfalls
#N/A — below the smallest value. If lookup_value is smaller than the first item of lookup_vector, there's nothing ≤ it to return.
Pitfall: unsorted data fails silently. LOOKUP assumes ascending order and doesn't check. Wrong order means wrong answers with no error — the most dangerous kind of bug.
Pitfall: expecting exact match. Looking up "Webcam Pro" in an unsorted product list is exactly what LOOKUP is wrong for. Use XLOOKUP, VLOOKUP with FALSE, or INDEX/MATCH.
Pitfall: mismatched vector sizes. lookup_vector and result_vector must be the same length, or results misalign.
Practice workbook
Frequently asked questions
What's the difference between LOOKUP and VLOOKUP?
Can LOOKUP do an exact match?
Why does =LOOKUP(9.99E+307, A:A) return the last number?
Should I still use LOOKUP in new workbooks?
What is the array form of LOOKUP?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class