VLOOKUP vs XLOOKUP in Google Sheets: Which to Use
For anyone who needs to pull a value from another table — a price, a status, an email — and isn't sure which lookup formula to reach for.
On this page
You have two tables. One has a client name; the other has that client's email or rate. You want to pull the matching value across without copy-pasting. That's a lookup — and in Google Sheets you'll usually choose between VLOOKUP and XLOOKUP.
Use XLOOKUP for new sheets: it's easier to read, can look to the left, and won't break when you insert a column. Use VLOOKUP when you're editing an older sheet that already uses it, or sharing with people on tools that don't support XLOOKUP. They solve the same problem — XLOOKUP just has fewer sharp edges.
The sample data#
Imagine a small lookup table on a tab called Rates:
| A (Client) | B (Hourly rate) |
|---|---|
| Avery & Co | 85 |
| Brightwork | 70 |
| Carter Studio | 95 |
You want to fetch a client's rate by name. Here's how each formula does it.
VLOOKUP: the original, with one sharp edge#
=VLOOKUP("Brightwork", Rates!A2:B4, 2, FALSE)Read it as: look for Brightwork in the first column of Rates!A2:B4, then return the value from column 2 of that range. The FALSE means "exact match only" — almost always what you want. Expected result: 70.
The catch with VLOOKUP is the 2. It's a column number, counted from the start of the range. If you later insert a column inside that range, the number no longer points where you think — and the formula returns the wrong value without any error.
The mistake that quietly breaks VLOOKUP
VLOOKUP can only look right of the search column, and its column index is positional. Insert or reorder columns and it silently returns the wrong cell. Always lock the range ($A$2:$B$4) before copying, and re-check the index after any structural change.
XLOOKUP: points at the column, not a number#
=XLOOKUP("Brightwork", Rates!A2:A4, Rates!B2:B4, "Not found")Read it as: look for Brightwork in the names range Rates!A2:A4, and return the matching cell from the rates range Rates!B2:B4. The last argument, "Not found", is shown instead of an error when there's no match. Expected result: 70.
Because you point at the result column directly (not a number), inserting columns doesn't break it. It can also look to the left, and the built-in "if not found" value is much friendlier than a raw #N/A.
Side by side#
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Points at the result by | Column number | Actual column range |
| Survives inserting a column | No | Yes |
| Can look left | No | Yes |
| Built-in "not found" message | No (returns #N/A) | Yes |
| Best when | Editing an older sheet | Building something new |
What to try first if you're unsure#
Reach for XLOOKUP. The only strong reasons to stay on VLOOKUP are a sheet that already uses it, or sharing with a tool that doesn't support XLOOKUP.
If your lookup returns #N/A, that usually means no exact match was found. Check for stray spaces or a status typed two different ways before assuming the formula is wrong — the same input-consistency problem covered in the small-business setup guide.
Questions people ask
Why does my VLOOKUP return the wrong value after I added a column?
What does #N/A mean in a lookup?
Is XLOOKUP always better than VLOOKUP?
We aim to keep this accurate and date-stamp it when product steps change. We don't claim hands-on product testing we haven't done, and we'll always point to the free, native route first. How we work.