# Excel Lookup Functions: VLOOKUP, XLOOKUP and INDEX-MATCH

The fastest way to answer "which lookup function should I use?" is this: if you have Microsoft 365 or Excel 2021 and later, use XLOOKUP for exact matches. If you are on an older version, use INDEX-MATCH when the return column sits to the left of the lookup column, and VLOOKUP when it sits to the right. All three return the same answer when written correctly, so the choice is about version support and how your table is arranged.

## Quick Answer

- **XLOOKUP** is the modern default. It returns exact matches by default, works in any direction, and needs a lookup array plus a return array [1].
- **VLOOKUP** searches the first column of a table array and moves right by a column index number. You must pass `FALSE` (or `0`) for an exact match [2].
- **INDEX-MATCH** combines MATCH to find a position and INDEX to return the value at that position. It can look left or right and does not care about column order [3].
- For an exact match in VLOOKUP, the fourth argument must be `FALSE` or `0`. Leaving it out defaults to approximate matching and can return the wrong row [4].
- All three formulas below return `$8.99` for product code `P004` from the same six-row table.

## Before You Start

Know your Excel version, because it decides which functions exist. XLOOKUP is available in Excel for Microsoft 365, Excel 2024, Excel 2021 and later, and it is not available in earlier versions [5]. VLOOKUP and INDEX-MATCH work in every version covered by Microsoft's reference, including Excel 2016 and Excel 2019 [5][2].

Know your table layout. VLOOKUP has one hard rule: the value you look up must sit in the first column of the range you give it. If your table spans `B2:D7`, your lookup value must be in column B [2]. If the column you want to return sits to the left of the lookup column, VLOOKUP cannot reach it without rearranging data, and INDEX-MATCH or XLOOKUP is the better fit.

Decide on exact versus approximate matching before you type anything. Exact matching means the lookup value must equal a value in the lookup column. Approximate matching returns the closest value that is less than or equal to the lookup value, and it depends on sorted data [6]. For product codes, names and IDs, you almost always want exact.

## Step by Step

1. **Put your lookup value in its own cell.** In the example below, cell `E2` holds the code you want to find. Keeping it separate means you can change it and watch all three formulas update.
2. **Write the VLOOKUP.** The syntax is `VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])` [2]. Use `=VLOOKUP(E2,A2:C7,3,FALSE)`. The `3` counts columns from the left edge of `A2:C7`, so column C is the third column. `FALSE` forces an exact match.
3. **Write the XLOOKUP.** The syntax uses a lookup array and a return array instead of one table plus an index [1]. Use `=XLOOKUP(E2,A2:A7,C2:C7)`. No match mode argument is needed because XLOOKUP produces an exact match by default [1].
4. **Write the INDEX-MATCH.** Use `=INDEX(C2:C7,MATCH(E2,A2:A7,0))`. MATCH returns the relative position of the code inside `A2:A7`, and the `0` means exact match. INDEX then returns the value from `C2:C7` at that position [7].
5. **Check the result against the source row.** Find the code manually in column A and read across to column C. If the formula output matches, the lookup is wired correctly.
6. **Copy the formula down if you have more codes.** Lock the ranges with absolute references before filling down, in all three formulas, so the arrays do not shift.

## Worked Example

The table below lists six products with a code, a name and a price. Cell `E2` holds the code `P004`, and three formulas in row 2 each look up its price.

| | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Product Code | Product Name | Price | | Lookup Code | VLOOKUP Price | XLOOKUP Price | INDEX-MATCH Price |
| 2 | P001 | Widget | 10.5 | | P004 | `=VLOOKUP(E2,A2:C7,3,FALSE)` -> displays $8.99 | `=XLOOKUP(E2,A2:A7,C2:C7)` -> displays $8.99 | `=INDEX(C2:C7,MATCH(E2,A2:A7,0))` -> displays $8.99 |
| 3 | P002 | Gadget | 15.75 | | | | | |
| 4 | P003 | Gizmo | 22 | | | | | |
| 5 | P004 | Doohickey | 8.99 | | | | | |
| 6 | P005 | Thingamajig | 12.3 | | | | | |
| 7 | P006 | Whatchamacallit | 19.99 | | | | | |

Each formula takes a different route to the same cell:

- **F2** searches the first column of `A2:C7` for `P004`, finds it in row 5 of the sheet, and returns the third column of the range, which is the price `8.99`.
- **G2** searches `A2:A7` for `P004` and returns the value at the matching position in `C2:C7`, again `8.99`.
- **H2** uses MATCH to locate `P004` at position 4 within `A2:A7`, then INDEX returns the fourth value of `C2:C7`, which is `8.99`.

The general shape of each formula is worth memorizing:

$$ \text{VLOOKUP}(v, \text{table}, n, \text{FALSE}) $$

$$ \text{XLOOKUP}(v, \text{lookup\_array}, \text{return\_array}) $$

$$ \text{INDEX}(\text{return\_array}, \text{MATCH}(v, \text{lookup\_array}, 0)) $$

## Other Ways to Do It

**HLOOKUP** does the same job horizontally. It searches the first row of a range and returns a value from a specified row below it. Microsoft's guidance for horizontal exact-match lookups points to HLOOKUP, and XLOOKUP can replace it as well [1][7].

**LOOKUP** in its array form searches by the shape of the array. If the array is wider than it is tall, it searches the first row, and if it is taller than it is wide, it searches the first column [6]. It requires ascending sort order in the lookup vector, so it is a poor choice for unsorted ID lists [6].

**Nested XLOOKUP** handles two-way lookups. Microsoft's own example nests one XLOOKUP inside another to match a row label and a column label at the same time, which is the modern equivalent of combining INDEX and MATCH [1].

If you are still deciding between the two most common options, the comparison of [XLOOKUP vs VLOOKUP differences](/blog/data-analysis/xlookup-vs-vlookup-differences) walks through direction, defaults and error handling side by side.

## Troubleshooting

**`#N/A` means no exact match was found.** Check for trailing spaces and numbers stored as text. Case differences do not matter, because these lookups are not case-sensitive. A code typed as `P004 ` with a space will not match `P004`.

**VLOOKUP returns a plausible but wrong value.** This almost always means the fourth argument was omitted or set to `TRUE`. The default is approximate matching, and if the lookup column is not sorted ascending, the function can return the wrong result [4]. Add `FALSE`.

**`#REF!` in VLOOKUP means the column index is too large.** If your table array is `A2:C7` and you ask for column 5, there is no fifth column in that range.

**INDEX-MATCH returns the wrong row after you copy it.** Relative references shift when you fill the formula down. Lock the arrays, for example `$A$2:$A$7` and `$C$2:$C$7`.

**XLOOKUP gives a `#NAME?` error.** That usually means the function is not available in your Excel version [5]. Fall back to INDEX-MATCH.

## Common Mistakes

- **Leaving out the fourth VLOOKUP argument.** The default is approximate matching, which can silently return the wrong row [4]. Always write `FALSE` or `0` for exact matches.
- **Counting the wrong column.** The column index counts from the first column of the table array, not from column A of the sheet. If your range starts at B, column C is index 2.
- **Assuming VLOOKUP can look left.** It cannot. The lookup value must be in the first column of the range you supply [2]. Use INDEX-MATCH or XLOOKUP when the return column is to the left.
- **Using LOOKUP on unsorted data.** The lookup vector must be in ascending order or LOOKUP may not return the correct value [6]. Use an exact-match function instead.
- **Hardcoding the lookup value.** Typing `"P004"` inside the formula means editing the formula every time. Reference a cell instead.
- **Ignoring data types.** A number stored as text will not match the same number stored as a number, and the formula returns `#N/A` even though the values look identical.

## Limitations

None of these functions can return a value that does not exist in the table. They match, they do not calculate. If your lookup column contains duplicates, VLOOKUP, XLOOKUP and MATCH all return the first match they encounter, so later duplicates are invisible to the formula. XLOOKUP can return the closest approximate match when no exact match exists, but that behavior has to be requested through the match mode argument, and a binary search mode requires the lookup array to be sorted or it returns invalid results [1].

Version support is the other real constraint. XLOOKUP does not exist in Excel 2016 or Excel 2019, so a workbook built with it will break for anyone on those versions [5]. INDEX-MATCH is the most portable exact-match pattern because it works across every version covered here and does not depend on column order [3].

## Frequently Asked Questions

### Which lookup function excel should I use for exact matches?

Use XLOOKUP if your version supports it, because exact matching is the default and no match mode argument is required [1]. Use INDEX-MATCH if you need compatibility with older versions or your return column sits to the left of your lookup column [3]. Use VLOOKUP if your table is already arranged with the lookup column on the left and you are comfortable passing `FALSE`.

### What is the lookup command in Excel?

There is no single lookup command. Lookup is a family of functions, including VLOOKUP, HLOOKUP, XLOOKUP, LOOKUP, INDEX and MATCH, all listed under lookup and reference functions in Microsoft's documentation [5]. You call them by typing a formula into a cell, starting with `=`.

### Why does my VLOOKUP return the wrong value?

The most common cause is a missing or incorrect fourth argument. When `range_lookup` is `TRUE` or omitted, VLOOKUP uses approximate matching, and if the lookup column is not sorted ascending the function might return the wrong result [4]. Set the fourth argument to `FALSE` for an exact match.

### Can INDEX-MATCH look up values to the left?

Yes. MATCH finds the position of the lookup value in any single row or column, and INDEX returns the value at that position from any other range [7]. Neither function requires the lookup column to be first, which is the main advantage over VLOOKUP.

### Is XLOOKUP always better than VLOOKUP?

For exact-match lookups in a supported version, XLOOKUP is simpler because it works in any direction and returns exact matches by default [5][1]. VLOOKUP still works fine when your data is arranged left to right and you need the file to open in older Excel versions. For a full breakdown, see [how to use XLOOKUP in Excel](/blog/data-analysis/how-to-use-xlookup-excel), and for the older pattern, [Excel INDEX MATCH step by step](/blog/data-analysis/excel-index-match). If you want to go deeper on the position-finding half of INDEX-MATCH, the [MATCH function syntax guide](/blog/data-analysis/excel-match-function-syntax) covers the match type argument in detail.

## References

1. [XLOOKUP function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/xlookup-function)
2. [VLOOKUP function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/vlookup-function)
3. [Use Excel built-in functions to find data in a table or a range of cells | Microsoft Support](https://support.microsoft.com/en-us/excel/use-excel-built-in-functions-to-find-data-in-a-table-or-a-range-of-cells)
4. [Use the table_array argument in a lookup function | Microsoft Support](https://support.microsoft.com/en-us/excel/use-the-table-array-argument-in-a-lookup-function)
5. [Lookup and reference functions (reference) | Microsoft Support](https://support.microsoft.com/en-us/excel/lookup-and-reference-functions-reference)
6. [LOOKUP function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/lookup-function)
7. [Look up values in a list of data in Excel | Microsoft Support](https://support.microsoft.com/en-us/excel/look-up-values-in-a-list-of-data-in-excel)

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

## Related Articles

- [XLOOKUP vs VLOOKUP: Differences and When to Use Each](/blog/data-analysis/xlookup-vs-vlookup-differences)
- [Excel MATCH Function: Syntax and Examples](/blog/data-analysis/excel-match-function-syntax)
- [VLOOKUP in Excel: Formula, Syntax and Examples](/blog/data-analysis/vlookup-excel-formula-syntax-examples)
- [Excel INDEX MATCH: How to Look Up Values Step by Step](/blog/data-analysis/excel-index-match)
- [Excel FIND Function: Syntax, Examples and SEARCH Comparison](/blog/data-analysis/excel-find-function-syntax-examples)