# LOOKUP function in Excel

> LOOKUP finds a value in a sorted row or column and returns the value in the same position of another row or column.

Source: https://spreadsheetpractice.com/functions/lookup · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Quick answer

- **What it does:** 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%)
- **Available in:** Every current version
- **Category:** [Lookup functions](https://spreadsheetpractice.com/functions/category/lookup)
- **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.

## What it does

LOOKUP finds a value in a sorted row or column and returns the value in the same position of another row or column. 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.

## 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 was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Min sales | Rate |  | Rep | Sales | Rate |  | Last Ana sale |
| 2 | 0 | 2% |  | Ana | $8,200 | 4% |  | $22,000 |
| 3 | 5000 | 4% |  | Raj | $14,500 | 6% |  |  |
| 4 | 10000 | 6% |  | Lee | $3,100 |  |  |  |
| 5 | 20000 | 8% |  | Ana | $22,000 |  |  |  |

## 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.

## Related functions

- [XLOOKUP](https://spreadsheetpractice.com/functions/xlookup): XLOOKUP searches one column or row for a value and returns the matching item from another.
- [VLOOKUP](https://spreadsheetpractice.com/functions/vlookup): VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row.
- [MATCH](https://spreadsheetpractice.com/functions/match): MATCH returns the position of a value in a row or column: 3 means the third cell.

## 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.

## Practice

A graded LOOKUP exercise ("Shipping rate by weight") is on [the LOOKUP page](https://spreadsheetpractice.com/functions/lookup). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.