LOOKUP function
Quick answer
LOOKUP finds a value in a sorted row or column and returns the value in the same position of another row or column.
- Syntax
=LOOKUP(lookup_value, lookup_vector, [result_vector])- Most common use
=LOOKUP(E2,$A$2:$A$5,$B$2:$B$5)returns 4% ($8,200 falls in the $5,000 tier: 4%)
It always does an approximate match, so it suits tier and bracket tables, and the LOOKUP(2,1/(range=value),results) pattern returns the last match in a list. For exact lookups, XLOOKUP or INDEX and MATCH are safer.
- Available in
- Every current version
- Category
- Lookup functions
- Returns
- The value in the position of the largest entry that is less than or equal to lookup_value; #N/A if lookup_value is smaller than every entry.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=LOOKUP(lookup_value, lookup_vector, [result_vector])
| Argument | Required? | What it is |
|---|---|---|
lookup_value | Required | The value to look for. |
lookup_vector | Required | One row or column to search, sorted from smallest to largest. |
result_vector | Optional | Optional: the row or column to return a value from, the same size as lookup_vector. If omitted, the value comes from lookup_vector. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =LOOKUP(E2,$A$2:$A$5,$B$2:$B$5) | 4% | $8,200 falls in the $5,000 tier: 4% |
| F3 | =LOOKUP(E3,$A$2:$A$5,$B$2:$B$5) | 6% | $14,500 is past $10,000 but short of $20,000: 6% |
| H2 | =LOOKUP(2,1/(D2:D5="Ana"),E2:E5) | $22,000 | Ana's last sale in the list, not her first |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Min sales | Rate | Rep | Sales | Rate | Last Ana sale | ||
| 0 | 2% | Ana | $8,200 | 4% | $22,000 | ||
| 5000 | 4% | Raj | $14,500 | 6% | |||
| 10000 | 6% | Lee | $3,100 | ||||
| 20000 | 8% | Ana | $22,000 |
Practice LOOKUP
Graded instantly, then re-checked on changed data.
Shipping rate by weight
E2, look up the shipping rate for each parcel weight in D2 from the table in A2:B6: the rate of the largest weight band that isn't more than the parcel's weight. Fill down to E7.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| A wrong value with no error | LOOKUP assumes the lookup row or column is sorted from smallest to largest. On unsorted data it can return the wrong row silently. Sort it, or use XLOOKUP. |
| #N/A | The value is smaller than the first entry. Start tier tables at 0 so every amount has a tier. |
| A near match instead of an exact one | LOOKUP never does an exact match, so "Smith" can return the row for "Smit". Use XLOOKUP or MATCH with 0 for exact lookups. |
Tips
- =LOOKUP(2,1/(A2:A100<>""),A2:A100) returns the last non-empty value in a column: the 1/(…) turns every other row into an error, which LOOKUP skips.
Questions
What is the difference between LOOKUP and VLOOKUP?
VLOOKUP searches the first column of a table and can match exactly with FALSE. LOOKUP takes two separate rows or columns and only matches approximately.
How does LOOKUP(2,1/(…)) find the last match?
1/(condition) is 1 where the condition is TRUE and #DIV/0! everywhere else. LOOKUP skips errors, and since 2 is bigger than every 1, it settles on the last one.
Is LOOKUP still worth using?
For tier tables and the last-match pattern, yes. For everything else XLOOKUP is clearer and matches exactly by default.
Updated Checked by running: every formula on this page was run by our formula engine on .