Skip to content

Excel function · Lookup & reference

Excel LOOKUP function

LOOKUP finds the last value in a sorted list that is less than or equal to what you look for and returns the value in the same position of another column: a commission rate from sales, the last entry in a column.

In Turkish Excel: ARA

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

English Excel=LOOKUP(lookup_value, lookup_vector, [result_vector])
Turkish Excel=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

ArgumentRequiredDescription
lookup_valueRequiredThe 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_vectorRequiredA single column or row sorted from smallest to largest, e.g. the band lower bounds $F$2:$F$5.
result_vectorOptionalThe 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.

C2fx=LOOKUP(B2,$F$2:$F$5,$G$2:$G$5)
Commission rate and amount from sales: example table
ABCDEFG
1RepSalesRateCommissionSales FromRate
2Emma18,5001.0%185.0001.0%
3Liam42,0003.5%1,470.0020,0002.0%
4Olivia75,5005.0%3,775.0040,0003.5%
5Noah20,0002.0%400.0060,0005.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.

D2fx=LOOKUP(2,1/(B2:B7<>""),B2:B7)
Find the last entry in a column: example table
ABCDE
1DateCountLast CountLast Count Date
226-Oct-20261,2401,10229-Oct-2026
327-Oct-20261,185
428-Oct-2026
529-Oct-20261,102
630-Oct-2026
72-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

Type this in Nexell

Add each rep's commission rate from the commission table based on their sales, and calculate the commission amount

Convert to table

Bring 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

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free