Stop Using VLOOKUP: How to Use XLOOKUP in Excel (With Examples)

# Stop Using VLOOKUP: How to Use XLOOKUP in Excel

**Watch the 1-minute video:**

If you’ve used VLOOKUP for a while, you know its problems. It needs a column number, it breaks when someone inserts a column, it can’t look to the left, and it fills your sheet with `#N/A` errors. **XLOOKUP** fixes all of that, and it’s easier to read.

## The sample sheet

| | A | B | C | D | E | F | G |

|—|—|—|—|—|—|—|—|

| 1 | ID | Name | Department | Ext | | Find | Result |

| 2 | E101 | Abd Almouhimen Shamout | IT Support | 2101 | | E103 | |

| 3 | E102 | Abd Shamout | Network | 2102 | | | |

| 4 | E103 | Mouhimen Dev | Web Dev | 2103 | | | |

| 5 | E104 | Abd A. Shamout | Help Desk | 2104 | | | |

Download it as CSV: [excel-xlookup-sample.csv](#) *(attach the file when you publish)*

## The old way: VLOOKUP

“`excel

=VLOOKUP(F2,A1:D5,2,FALSE)

“`

This returns **Mouhimen Dev**. But the `2` is a hard-coded column number. Insert a new column between A and B (say, Email), and the formula silently returns the email instead of the name. No error, just wrong data. VLOOKUP also can’t return a value from a column to the **left** of the one it searches.

## XLOOKUP in 3 parts

“`excel

=XLOOKUP(F2,A2:A5,B2:B5)

“`

1. **What you’re looking for:** `F2` (the ID, E103)

2. **Where to look:** `A2:A5` (the ID column)

3. **What to return:** `B2:B5` (the Name column)

Result: **Mouhimen Dev**. No column number to count, and if you insert a column, the ranges move with it, so the formula keeps working.

## It looks left, too

Put a name in F2 and swap the two ranges:

“`excel

=XLOOKUP(F2,B2:B5,A2:A5)

“`

It searches the Name column and returns the ID from column A, which is to the left. VLOOKUP can’t do that.

## Bonus: replace #N/A with your own text

The 4th argument is what to show when nothing is found:

“`excel

=XLOOKUP(F2,A2:A5,B2:B5,”Not found”)

“`

Look up `E999` and you get **Not found** instead of `#N/A`. No more wrapping everything in IFERROR.

## Full syntax

“`excel

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

“`

– `match_mode`: 0 exact match (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.

– `search_mode`: 1 first to last (default), -1 last to first (great for “find the latest entry”).

## Which Excel versions have XLOOKUP?

XLOOKUP works in **Microsoft 365, Excel 2021 and newer, and Excel for the web**. It is **not** available in Excel 2019 or Excel 2016. If you share files with people on older versions, they’ll see an error. Use `INDEX` + `MATCH` for those files.

## Quick checklist

– [ ] Replace `VLOOKUP(x, table, n, FALSE)` with `XLOOKUP(x, key_column, result_column)`

– [ ] Use whole ranges of the same size for lookup and return

– [ ] Add `”Not found”` (or `””`) as the 4th argument

– [ ] Check that everyone who opens the file has Excel 2021 or Microsoft 365

## Sources

– Microsoft Support: [XLOOKUP function](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)

—

**Watch the video:**

*Abd Almouhimen Shamout, Take away support: IT fixes, delivered in under two minutes. More at mouhimen.dev.*