# VLOOKUP in Excel: Formula, Syntax and Examples

The VLOOKUP formula in Excel finds a value in the first column of a table and returns a value from another column in the same row. You give it four pieces of information: what to look up, where to look, which column to return, and whether you want an exact or approximate match. This article covers the syntax, a worked example, the errors you will hit, and when another function is a better choice.

## Quick Answer

- The full form is `=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)` [1].
- `lookup_value` is what you search for, and it must sit in the first column of `table_array` [1].
- `col_index_num` counts columns from the left edge of `table_array`, starting at 1 [1].
- Use `FALSE` (or `0`) for an exact match, which is what most lookups need [1].
- If the exact value is not found with `FALSE`, Excel returns the `#N/A` error [1].

## Syntax

The function takes four arguments. The first three are required, and the fourth is optional but you should almost always supply it.

| Argument | Required? | Meaning |
|---|---|---|
| `lookup_value` | Yes | The value you want to find. It is matched against the first column of `table_array` [1]. |
| `table_array` | Yes | The range or table that contains the data. The lookup column must be the leftmost column of this range [1]. |
| `col_index_num` | Yes | The column number within `table_array` that holds the value to return. Column 1 is the first column of the range [1]. |
| `range_lookup` | No | `TRUE` or `1` for an approximate match, `FALSE` or `0` for an exact match [1]. |

Written as a formula, the pattern looks like this:

$$=\text{VLOOKUP}(\text{lookup\_value},\ \text{table\_array},\ \text{col\_index\_num},\ \text{range\_lookup})$$

Microsoft describes the simplest form as: what you want to look up, where you want to look for it, the column number in the range containing the value to return, and an approximate or exact match indicated as 1/TRUE or 0/FALSE [1].

## How It Works

VLOOKUP reads down the first column of your range until it finds the lookup value. Once it finds a match, it moves across that same row to the column you named and returns whatever is in that cell.

The "V" stands for vertical. The function searches down a column, so your lookup values have to be arranged vertically in the leftmost column of the range you pass in. If the value you want back sits to the left of the lookup column, VLOOKUP cannot reach it. That is one of the main reasons people switch to other tools, which are compared in [Excel lookup functions: VLOOKUP, XLOOKUP and INDEX-MATCH](/blog/data-analysis/excel-lookup-functions).

The fourth argument changes the behavior completely. With `FALSE`, Excel looks for an exact match and returns `#N/A` when nothing matches [1]. With `TRUE`, Excel assumes the first column is sorted in ascending order and returns the closest match that does not go past the lookup value. For text lookups and ID lookups, always use `FALSE`.

One practical detail: when you copy a formula down a column, lock the table range with dollar signs, as in `$E$2:$F$6`. The lookup value should stay relative so it shifts row by row.

## Worked Example

The sheet below holds a sales list in columns A to D and a separate price table in columns E and F. Column C uses VLOOKUP to fetch each product's price from the price table.

| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Product ID | Product | Price | Sales Rep | Product ID | Price |
| 2 | P-100 | Widget | `=VLOOKUP(A2,$E$2:$F$6,2,FALSE)` -> displays $9.99 | Ana | P-100 | 9.99 |
| 3 | P-102 | Gadget | `=VLOOKUP(A3,$E$2:$F$6,2,FALSE)` -> displays $7.25 | Ben | P-101 | 14.5 |
| 4 | P-104 | Gizmo | `=VLOOKUP(A4,$E$2:$F$6,2,FALSE)` -> displays $5.50 | Cara | P-102 | 7.25 |
| 5 | P-101 | Doohickey | `=VLOOKUP(A5,$E$2:$F$6,2,FALSE)` -> displays $14.50 | Dan | P-103 | 22 |
| 6 | P-103 | Thingamajig | `=VLOOKUP(A6,$E$2:$F$6,2,FALSE)` -> displays $22.00 | Eve | P-104 | 5.5 |

The formula in C2 does four things. It takes the product ID in A2 as the lookup value. It searches the first column of `$E$2:$F$6`, which is column E. It returns the value from column 2 of that range, which is column F. The `FALSE` at the end forces an exact match.

Because the range is locked with `$` signs, you can copy C2 down to C6 and every row keeps pointing at the same price table while the lookup value moves down with it. Row 3 looks up P-102 and returns 7.25. Row 4 looks up P-104 and returns 5.5. Row 5 looks up P-101 and returns 14.5. Row 6 looks up P-103 and returns 22.

Notice that the order of the sales list does not matter. The price table is sorted by product ID, but VLOOKUP with `FALSE` does not care about sort order. It scans until it finds the exact match.

## More Examples

**Look up a value in a different workbook or sheet.** Point `table_array` at a sheet reference, as in `=VLOOKUP(A2,Products!$A$2:$D$500,3,FALSE)`. The same rule applies: the lookup column must be the leftmost column of the range you name.

**Return a column far to the right.** If your table runs from column A to column H and you want the value in H, use 8 as `col_index_num`, because H is the eighth column from A.

**Wrap it in IFERROR for a cleaner output.** `=IFERROR(VLOOKUP(A2,$E$2:$F$6,2,FALSE),"Not found")` shows a friendly label instead of `#N/A` when a product ID is missing. This is common in reports where blank or missing rows would otherwise look broken.

**Look up text instead of IDs.** `=VLOOKUP("Fontana",B2:E7,2,FALSE)` returns the value in the second column of the range for the row where the first column contains Fontana. Microsoft uses exactly this pattern to show that text lookups need quotes around the value [1].

**Use it for horizontal data.** If your data runs across rows instead of down columns, VLOOKUP will not work. Use [How to Use HLOOKUP in Excel (With Examples)](/blog/data-analysis/how-to-use-hlookup-excel) instead, since HLOOKUP searches across the top row of a range.

## Errors and How to Fix Them

| Error | What it means | Fix |
|---|---|---|
| `#N/A` | The exact value was not found with `FALSE` [1]. | Check for trailing spaces, mismatched data types, or a value that genuinely is not in the table. |
| `#REF!` | `col_index_num` is larger than the number of columns in `table_array` [1]. | Count the columns in your range and lower the number. |
| `#VALUE!` | `col_index_num` is less than 1 [1]. | Use a column number of 1 or more. |
| `#NAME?` | The formula is usually missing quotes around a text value [1]. | Put quotes around text, as in `=VLOOKUP("Fontana",B2:E7,2,FALSE)` [1]. |

The `#N/A` error is by far the most common. In practice it usually means the lookup value and the table value look identical but are not. A number stored as text will not match a real number, and a trailing space will break an otherwise perfect match.

## Common Mistakes

- **Leaving out the fourth argument.** If you omit `range_lookup`, Excel uses an approximate match, which can return a wrong value from a nearby row. Always type `FALSE` explicitly.
- **Forgetting to lock the table range.** Copying a formula down without `$` signs shifts the range and produces wrong or missing results. Use `$E$2:$F$6` style references.
- **Counting columns from the sheet instead of the range.** `col_index_num` counts from the left edge of `table_array`, not from column A. If your range starts at E, then E is column 1 [1].
- **Looking up a value that sits to the right of the return column.** VLOOKUP only searches the first column of the range and only returns from columns to its right. Rearrange the data or use a different function.
- **Mixing text and numbers.** A product ID stored as text in one table and as a number in another will never match. Convert both sides to the same type.
- **Using approximate match on unsorted data.** With `TRUE`, the first column must be sorted ascending. On unsorted data the result is unreliable.

## Limitations

VLOOKUP only looks to the right. The lookup column must be the leftmost column of the range, so any value to its left is unreachable without rearranging your sheet. It also returns only the first match it finds, so duplicate lookup values silently produce the first hit and hide the rest.

The function is also fragile when columns move. If someone inserts a column inside your table range, `col_index_num` no longer points at the column you intended, and the formula returns the wrong data without any warning. Microsoft now recommends XLOOKUP as an improved version that works in any direction and returns exact matches by default [1]. If you are on a version that supports it, see [XLOOKUP vs VLOOKUP: Differences and When to Use Each](/blog/data-analysis/xlookup-vs-vlookup-differences) before committing to VLOOKUP for new work. For matching a position rather than returning a value, the [Excel MATCH function: syntax and examples](/blog/data-analysis/excel-match-function-syntax) is the better fit.

## Frequently Asked Questions

### How do you use a VLOOKUP in Excel?

Type `=VLOOKUP(` and then supply four arguments separated by commas: the value to find, the range to search, the column number to return, and `FALSE` for an exact match [1]. Press Enter and the result appears in the cell. Copy the formula down if you need it for several rows, and lock the table range with dollar signs first.

### Why does my VLOOKUP return #N/A when the value is clearly there?

The two values are almost certainly different types or contain hidden characters. A number stored as text will not match a real number, and a trailing space breaks an exact match. Check both cells with a formula that measures length, or clean the data before looking up.

### Can VLOOKUP look to the left?

No. VLOOKUP searches the first column of the range you give it and returns values from columns to the right of that one [1]. If the value you want sits to the left of the lookup column, move the columns, or use INDEX and MATCH or XLOOKUP instead.

### What is the difference between TRUE and FALSE in VLOOKUP?

`FALSE` means exact match and returns `#N/A` when nothing matches [1]. `TRUE` means approximate match, which requires the first column to be sorted ascending and returns the closest value that does not exceed the lookup value. For IDs, names and codes, use `FALSE`.

### Does VLOOKUP work in older .xls files?

Yes. VLOOKUP has been part of Excel for many versions, so formulas built in current files open and calculate in older `.xls` workbooks. The function name and the four arguments are the same. Features added later, such as XLOOKUP, are not available in those older formats.

If you are building a lookup layer for a larger report, pair VLOOKUP with aggregation tools such as [Excel SUMIF and SUMIFS: syntax and examples](/blog/data-analysis/excel-sumif-sumifs-syntax-examples) so the returned values feed straight into your totals.

## References

1. [VLOOKUP function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/vlookup-function)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)
- [McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis](https://doi.org/10.1016/j.csda.2008.03.004)
- [Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1008984)

## Related Articles

- [How to Use HLOOKUP in Excel (With Examples)](/blog/data-analysis/how-to-use-hlookup-excel)
- [Excel Lookup Functions: VLOOKUP, XLOOKUP and INDEX-MATCH](/blog/data-analysis/excel-lookup-functions)
- [Excel MATCH Function: Syntax and Examples](/blog/data-analysis/excel-match-function-syntax)
- [XLOOKUP vs VLOOKUP: Differences and When to Use Each](/blog/data-analysis/xlookup-vs-vlookup-differences)
- [Excel SUBTOTAL Function: Syntax, Formulas and Examples](/blog/data-analysis/excel-subtotal-function-formula)
- [Pivot Table in Excel: Step-by-Step Tutorial](/blog/research-skills/pivot-table-in-excel-step-by-step-tutorial)
- [Tabular Data: What It Is and How to Analyze It](/blog/research-skills/tabular-data-what-it-is-and-how-to-analyze-it)