# VLOOKUP function in Excel

> VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row.

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

## Quick answer

- **What it does:** VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row.
- **Syntax:** `=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`
- **Most common use:** `=VLOOKUP("E-103",A2:C5,2,FALSE)` returns Lee Park (Exact match: the name for an employee ID)
- **Available in:** Every current version
- **Category:** [Lookup functions](https://spreadsheetpractice.com/functions/category/lookup)
- **Returns:** The value from the matching row, or #N/A when there is no match.
- **Google Sheets:** Works the same way in Google Sheets.

## What it does

VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row. It is the classic Excel lookup and still sits in millions of files, but it can only look to the right, relies on a column number that breaks when columns move, and does an approximate match unless you add FALSE.

## Syntax

`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`

| Argument | Required? | What it is |
|---|---|---|
| `lookup_value` | Required | The value to find in the first column of the table. |
| `table_array` | Required | The table to search. The column you search must be its leftmost column. |
| `col_index_num` | Required | Which column of the table to return, counting the first column as 1. |
| `range_lookup` | Optional | FALSE for an exact match. TRUE or left out for an approximate match, which needs the first column sorted smallest to largest. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| H2 | `=VLOOKUP("E-103",A2:C5,2,FALSE)` | Lee Park | Exact match: the name for an employee ID |
| H3 | `=VLOOKUP(12000,E2:F5,2,TRUE)` | 5% | Approximate match: $12,000 falls in the $10,000 tier, so the rate is 5% |
| H4 | `=VLOOKUP("E-110",A2:C5,3,FALSE)` | #N/A | An ID that isn't in the table returns the #N/A error |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Emp ID | Name | Dept |  | Sales from | Commission |  | Result |
| 2 | E-101 | Ana Ruiz | Sales |  | $0 | 2% |  | Lee Park |
| 3 | E-102 | Raj Patel | Finance |  | $5,000 | 3% |  | 5% |
| 4 | E-103 | Lee Park | Sales |  | $10,000 | 5% |  | #N/A |
| 5 | E-104 | Kim Osei | Ops |  | $25,000 | 7% |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| A wrong answer with no error | The fourth argument was left out, so VLOOKUP did an approximate match on unsorted data. Add FALSE for exact matches. |
| #N/A | The value isn't in the first column, or it differs by a trailing space or by being text in one place and a number in the other. Clean the data with TRIM or VALUE. |
| #REF! | col_index_num is larger than the number of columns in table_array. Widen the table range or lower the number. |
| Results break after inserting a column | The column number doesn't move, so it now points at the wrong column. XLOOKUP and INDEX/MATCH reference the return column directly and don't have this problem. |

## Tips

- Lock the table with $ ($A$2:$C$5) before filling the formula down.
- For a tier table, sort the first column smallest to largest and use TRUE: VLOOKUP returns the row for the largest value that is less than or equal to the lookup value.

## Related functions

- [XLOOKUP](https://spreadsheetpractice.com/functions/xlookup): XLOOKUP searches one column or row for a value and returns the matching item from another.
- [INDEX](https://spreadsheetpractice.com/functions/index): INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.
- [MATCH](https://spreadsheetpractice.com/functions/match): MATCH returns the position of a value in a row or column: 3 means the third cell.
- [HLOOKUP](https://spreadsheetpractice.com/functions/hlookup): HLOOKUP is VLOOKUP turned sideways: it searches the top row of a table for a value and returns a value from a row below it, in the same column.
- Comparison: [VLOOKUP vs XLOOKUP](https://spreadsheetpractice.com/functions/compare/vlookup-vs-xlookup)

## Questions

### What is the difference between VLOOKUP and XLOOKUP?

XLOOKUP takes separate search and return ranges, so it can look left and doesn't break when columns move, and it defaults to an exact match. Choose XLOOKUP in Excel 2021 or later; use INDEX/MATCH when the file must work in older versions.

### Why does VLOOKUP return the wrong value?

Usually because range_lookup was left out, so it did an approximate match. Add FALSE as the fourth argument.

### Can VLOOKUP look to the left?

No. The search column must be the first column of the table. Use XLOOKUP or INDEX and MATCH to return a column to its left.

## Practice

A graded VLOOKUP exercise ("Look up product names") is on [the VLOOKUP page](https://spreadsheetpractice.com/functions/vlookup). It is checked instantly, then re-checked on changed data.

Lessons that use VLOOKUP:

- [Lesson 12: VLOOKUP](https://spreadsheetpractice.com/learn/vlookup) (Pro)
- [Lesson 15: Tiered lookups & IFERROR](https://spreadsheetpractice.com/learn/tiered-lookups-iferror) (Pro)
- [Lesson 42: Handling errors on purpose](https://spreadsheetpractice.com/learn/errors-iferror-ifna) (Pro)

---

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