VLOOKUP and XLOOKUP both search for a value in one column and return a related value from the same row, such as a price for a product code. XLOOKUP is the newer and more flexible of the two, so it is the better default, while VLOOKUP remains common in older files you may inherit.
Both are covered here with examples you can copy, plus the mistakes that cause most lookup errors.
What a lookup actually does
Imagine a sheet of orders with a product code in column A, and a separate tab with a price list: codes in column A and prices in column B. A lookup takes each code in your orders, finds it in the price list and returns the matching price. Without it you would copy and paste prices by hand and hope nothing changes.
The value you search for is called the search key. It needs to match the entry in the lookup table exactly, which is why tidy data matters. If one side has a trailing space or a number stored as text, the match fails. Our guide to cleaning messy data in Google Sheets covers those fixes.
How VLOOKUP works
The syntax is =VLOOKUP(search_key, range, index, [is_sorted]).
- search_key is the value to find, such as
A2. - range is the table to search, such as
Prices!A:B. The search key is always looked for in the first column of this range. - index is the column number within the range to return. In
Prices!A:B, column B is number 2. - is_sorted should almost always be
FALSE, which asks for an exact match.
A complete example is =VLOOKUP(A2, Prices!A:B, 2, FALSE).
Where VLOOKUP trips people up
Leaving out the last argument is the classic mistake. If you omit it, or set it to TRUE, VLOOKUP assumes the first column is sorted and may return a wrong result without any warning. Always type FALSE when you want an exact match.
The other limits are structural. VLOOKUP can only look to the right, because it searches the first column of the range and returns from a column further along. Its index is also a number, so if someone inserts a column into the lookup table, your formula may quietly return the wrong field.
How XLOOKUP works
XLOOKUP separates the place you search from the place you return, which removes both limitations. The syntax is =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]).
The same example becomes =XLOOKUP(A2, Prices!A:A, Prices!B:B, "Not found"). It searches column A of the price list and returns the value from column B. Because you point at the result column directly, inserting a column in the middle of the table does not break the formula, and the result can sit to the left of the search column if you need it to.
Handling missing matches
The fourth argument, missing_value, replaces the #N/A error you would otherwise see when nothing matches. With VLOOKUP you would wrap the formula in IFERROR or IFNA to get the same effect. With XLOOKUP it is built in.
Match and search options
By default XLOOKUP looks for an exact match, so you do not need to remember a FALSE. The optional match mode lets you ask for the next smaller or next larger item instead, which suits tax bands or discount tiers. The search mode lets you search from the last item upward, which helps when a value appears more than once and you want the most recent entry.
VLOOKUP and XLOOKUP side by side
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Looks left of the key column | No | Yes |
| Default match type | Approximate unless FALSE is set | Exact |
| Survives inserted columns | Often not | Yes |
| Built-in not-found message | No | Yes |
| Found in old files and tutorials | Very common | Less common |
If you are writing a new formula, we suggest XLOOKUP. If you are editing someone else’s file full of VLOOKUPs, there is usually no reason to rewrite them all, as long as each one ends in FALSE.
Applying a lookup to a whole column
Rather than copying a formula down thousands of rows, you can wrap it in ARRAYFORMULA so one cell fills the whole column: =ARRAYFORMULA(XLOOKUP(A2:A, Prices!A:A, Prices!B:B, "")). Place it in the first row under your header, and keep the cells below it empty or Sheets will report that it cannot expand. The same technique works with VLOOKUP, and it keeps new rows covered without any copying.
Other options worth knowing
Before XLOOKUP, many people used INDEX with MATCH: =INDEX(Prices!B:B, MATCH(A2, Prices!A:A, 0)). MATCH finds the row position, and INDEX returns the value at that position in another column. It also looks in any direction, and it works everywhere, so you will meet it often.
When you want every matching row rather than just the first, a lookup is the wrong tool. FILTER returns all rows that meet a condition, for example =FILTER(Orders!A:C, Orders!B:B="Acme"). And if you need to pull data across tabs with conditions, QUERY is powerful once you are comfortable with the basics from our beginner formulas guide.
Troubleshooting a lookup that fails
- Check for stray spaces with
=LEN(A2)compared with the lookup table, or wrap the key in TRIM. - Check that both sides are the same type. A number stored as text will not match a true number.
- Make sure the lookup range really contains the key, and that the key is in the first column for VLOOKUP.
- Use absolute references, such as
$A:$B, if the formula changes when copied. - Look for duplicates. Lookups return only the first match they find.
You can find more practical guides like this in our spreadsheets category.
Where to start
Make a small price list on one tab and a short order list on another, then write the same lookup twice, once with VLOOKUP and once with XLOOKUP. Insert a column into the price list and see which formula survives. That ten-minute experiment makes the difference clearer than any table.
Frequently asked questions
Is XLOOKUP available in Google Sheets?
Yes. Google Sheets includes XLOOKUP alongside VLOOKUP, HLOOKUP, INDEX and MATCH, and the syntax is the same as the one shown here.
Why does VLOOKUP return the wrong value?
Most often the last argument was left out or set to TRUE, which asks for an approximate match on sorted data. Set it to FALSE for an exact match.
Can a lookup return more than one result?
Not with VLOOKUP or XLOOKUP, which return the first match. Use FILTER to return every row that meets a condition.
What does #N/A mean in a lookup?
It means no match was found. Check for extra spaces, mismatched number and text formats, or a missing entry in the lookup table.
