What it does
LOOKUP is one of Excel's oldest lookup functions and always uses approximate match: it finds the largest value in the lookup column that is less than or equal to the lookup value. So the lookup column must be sorted from smallest to largest; there is no exact-match option.
For band tables (commission rates, shipping bands, grade ranges) it is short and readable: the lookup and result columns are given separately and no column number is needed. It also has a well-known pattern for the last filled cell in a column: =LOOKUP(2,1/(B2:B20<>""),B2:B20).
Do not use LOOKUP for codes or names: when the value is missing, it can return a nearby row instead of an error. For exact matches use XLOOKUP, VLOOKUP or INDEX with MATCH.
Syntax
=LOOKUP(lookup_value, lookup_vector, [result_vector])=ARA(aranan; arama_vektörü; [sonuç_vektörü])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
lookup_value | Required | The value to look for, or the cell that holds it. If it is smaller than the smallest value in the lookup column, #N/A is returned. |
lookup_vector | Required | A single column or row sorted from smallest to largest, e.g. the band lower bounds $F$2:$F$5. |
result_vector | Optional | The single column or row to return from; it must be the same size as lookup_vector. If omitted, the matching value of lookup_vector itself is returned. |
Examples
1Commission rate and amount from sales
The commission table is in F:G with the lower bounds sorted from smallest to largest. LOOKUP finds the largest bound that is less than or equal to the sales figure and returns the rate from column G; column D works out the commission.
=LOOKUP(B2,$F$2:$F$5,$G$2:$G$5)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Sales | Rate | Commission | Sales From | Rate | |
| 2 | Emma | 18,500 | 1.0% | 185.00 | 0 | 1.0% | |
| 3 | Liam | 42,000 | 3.5% | 1,470.00 | 20,000 | 2.0% | |
| 4 | Olivia | 75,500 | 5.0% | 3,775.00 | 40,000 | 3.5% | |
| 5 | Noah | 20,000 | 2.0% | 400.00 | 60,000 | 5.0% |
- C2 (filled down to C5)
=LOOKUP(B2,$F$2:$F$5,$G$2:$G$5) - D2 (filled down to D5)
=B2*C2
Liam's 42,000 sits between 40,000 and 60,000, so his rate is 3.5% and his commission 1,470.00. Noah sold exactly 20,000 and falls into the 20,000 band: 2.0%.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Find the last entry in a column
Some days are missing in a daily stock count. D2 returns the last filled count and E2 uses the same pattern to return its date; both use 1/(B2:B7<>"") as the lookup array, only the result column differs.
=LOOKUP(2,1/(B2:B7<>""),B2:B7)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Count | Last Count | Last Count Date | |
| 2 | 26-Oct-2026 | 1,240 | 1,102 | 29-Oct-2026 | |
| 3 | 27-Oct-2026 | 1,185 | |||
| 4 | 28-Oct-2026 | ||||
| 5 | 29-Oct-2026 | 1,102 | |||
| 6 | 30-Oct-2026 | ||||
| 7 | 2-Nov-2026 |
- D2
=LOOKUP(2,1/(B2:B7<>""),B2:B7) - E2
=LOOKUP(2,1/(B2:B7<>""),A2:A7)
The last two days have no count; the formula skips the blanks and returns the last filled value, 1,102, dated 29-Oct-2026. Enter a number in B6 and both results move to that day.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #N/A
Cause: The lookup value is smaller than the smallest value in lookup_vector; for example the band table starts at 10,000 and the sale is 8,500.
Fix: Start the band table with the smallest possible value, usually 0.
- Wrong result
Cause: The lookup column is not sorted, or the code is not in the list; LOOKUP returned a nearby row without an error.
Fix: Sort the list from smallest to largest; use an exact-match function for codes.
Good to know
- In the last-filled-cell pattern,
1/(B2:B20<>"")gives 1 for filled cells and #DIV/0! for empty ones. LOOKUP cannot find 2, skips the errors and returns the value at the position of the last 1. - To find the last number in a column, look up a huge number:
=LOOKUP(9.99E+307,B2:B20).
In Nexell
Get Nexell to do it from a sentence
Add each rep's commission rate from the commission table based on their sales, and calculate the commission amount
Convert to tableBring the sales list and the commission table into the tool and type this. Nexell builds the band match and the commission amount with real Excel formulas; the preview shows each rep's rate.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between LOOKUP and VLOOKUP?
VLOOKUP takes a table range and a column number, and can do exact or approximate match. LOOKUP takes the lookup and result columns separately, and the result column can be on the left, but it only does approximate match and needs a sorted list.
How do I find the last filled cell in a column in Excel?
=LOOKUP(2,1/(B2:B100<>""),B2:B100) returns the last filled value in column B; extend the range to the row your list may grow to. To get another column on the same row, put that column as the third argument.
Related functions
- VLOOKUPDÜŞEYARA · Looks up a value in the first column and returns a value from the same row. Use 0 as the last argument for exact match.
- XLOOKUPÇAPRAZARA · Searches an array and returns the matching item; the fourth argument if not found.
- HLOOKUPYATAYARA · Looks up in the first row and returns from the same column.
- MATCHKAÇINCI · Position of a value in a list (0 = exact match).
- INDEXİNDİS · Returns the value at a given row and column of a range.