Lookup function
Excel LOOKUP function
LOOKUP finds the largest value that is less than or equal to the one you give it, in a row or column sorted smallest to largest, and returns the item in the same position of a second row or column. It is the shortest way to look up a band or tier, and it powers the classic formulas for the last match and the last number in a column.
=LOOKUP(lookup_value, lookup_vector, [result_vector])Arguments
lookup_value- The value to find. If there is no exact match, LOOKUP uses the largest value below it.
lookup_vector- One row or one column to search, sorted smallest to largest. Errors and values of a different type are skipped.
result_vector- Optional. The row or column to return from, the same size as lookup_vector. Leave it out to use the array form: LOOKUP(x, table) searches the first column of a tall table and returns from its last column.
LOOKUP examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from ($) | Rate | Date | Rep | Amount ($) | |
| 2 | 0 | 0.02 | Oct 1, 2026 | Kim | 4200 | |
| 3 | 5000 | 0.04 | Oct 2, 2026 | Raj | 12500 | |
| 4 | 10000 | 0.06 | Oct 5, 2026 | Kim | 7300 | |
| 5 | 20000 | 0.08 | Oct 6, 2026 | Ana | 21800 | |
| 6 | Oct 7, 2026 | Kim | 9600 | |||
| 7 | Oct 8, 2026 | Raj | 3150 | |||
| 8 | Oct 9, 2026 | Ana |
Download this sheet as an Excel practice file (.xlsx)
Result: 0.06
No tier starts at exactly 12,500, so LOOKUP takes the largest one below it, 10,000, and returns its rate: 0.06, which shows as 6%.
Result: 0.08
The array form: with no result_vector, LOOKUP searches the first column of A2:B5 and returns from the last one. 21,800 is past the 20,000 tier, so the rate is 0.08.
Result: 9600
The last match: 1/(E2:E8="Kim") is 1 on Kim’s rows and #DIV/0! elsewhere. LOOKUP cannot find 2, skips the errors and settles on the last 1, so it returns Kim’s latest amount, 9,600.
Result: 3150
The last number in a column: no amount is that big, so LOOKUP returns the last number it finds, 3,150. The blank F8 is skipped.
How to use LOOKUP
- Sort the lookup column smallest to largest (Data > Sort A to Z).
- Click the cell for the result.
- Type =LOOKUP( then the value to find, the lookup column and the result column, separated by commas.
- Press Enter.
Common LOOKUP mistakes
Unsorted lookup data
LOOKUP assumes the values go up from top to bottom. On unsorted data it can return a wrong answer with no error. Sort the column, or use XLOOKUP or INDEX/MATCH with an exact match.
Expecting #N/A when there is no exact match
LOOKUP always takes the closest smaller value, so a missing ID returns the item just before it. For IDs and names, use XLOOKUP or VLOOKUP with FALSE, which return #N/A when nothing matches.
A value below the first one
If lookup_value is smaller than every value in the list, LOOKUP returns #N/A. Start a tier table at 0 so every amount has a band.
LOOKUP FAQ
What is the difference between LOOKUP and VLOOKUP?
VLOOKUP searches the first column of a table and returns a column by number, with an exact-match option. LOOKUP only does an approximate match on sorted data, but its result can be any row or column of the same size, even one to the left.
How do I find the last matching value in Excel?
Use =LOOKUP(2,1/(A2:A100="Kim"),B2:B100). It works in every version without Ctrl+Shift+Enter. In Excel 365, =XLOOKUP("Kim",A2:A100,B2:B100,,0,-1) searches from the bottom and reads more clearly.
How do I get the last number in a column?
=LOOKUP(9.99E+307,B:B) returns the last number in column B, ignoring blanks and text. For the last text value, use =LOOKUP(REPT("z",255),B:B).
Guides that use LOOKUP
Related functions
XLOOKUPModern lookup in any directionVLOOKUPVertical lookupMATCHPosition of a valueINDEXValue at a position
Updated