How to Use XLOOKUP in Google Sheets: Replace VLOOKUP, Look Left, Handle Misses

XLOOKUP in Google Sheets finds a value in one column (or row) and returns the matching entry from another. This guide shows how to use it in place of VLOOKUP, look up to the left, return a whole row, show your own text when nothing matches, and pick the last match instead of the first.

Everything here comes from Google's XLOOKUP help page. That page has no date on it and was read in late October 2026. Try each formula on a copy of your data before relying on it.

The Sheets syntax, not the BigQuery one

Google's help page starts with a block titled "XLOOKUP for BigQuery". It uses different argument names (search_column, result_column) and only five arguments, and it notes that search_mode isn't supported in BigQuery. Skip it if you're working in a normal spreadsheet. The Sheets syntax is the second block:

=XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)

• search_key: what you're looking for, such as 42, "Cats" or a cell like B24.
• lookup_range: where to look. It must be a single row or a single column.
• result_range: what to return. Its size should match lookup_range.
• missing_value (optional): what to show if nothing matches. Default is #N/A.
• match_mode (optional): 0 exact (default), 1 exact or next larger, -1 exact or next smaller, 2 wildcard.
• search_mode (optional): 1 first to last (default), -1 last to first, 2 binary search on an ascending-sorted range, -2 binary search on a descending-sorted range.

Step 1: Do a basic exact lookup

Say column A holds product names and column E holds prices. To get the price for Apple:

1. Click the cell where you want the answer.
2. Type the formula below and press Enter.

=XLOOKUP("Apple", A2:A, E2:E)

To look up whatever is typed in a cell instead, swap the text for a reference, such as B24. With no other arguments, XLOOKUP looks for an exact match and searches top to bottom.

Step 2: Replace an existing VLOOKUP

Google gives this direct swap. The VLOOKUP version needs the whole table and a column number:

=VLOOKUP("Apple", A2:E, 5, FALSE)

The XLOOKUP version points straight at the column you want back:

=XLOOKUP("Apple", A2:A, E2:E)

The practical gain: if someone inserts a column inside the table, you aren't left with a hard-coded 5 that now points at the wrong column. HLOOKUP converts the same way:

=HLOOKUP("Price", A1:E6, 6, FALSE)
=XLOOKUP("Price", A1:E1, A6:E6)

Step 3: Look up to the left

VLOOKUP only returns columns to the right of the one it searches. If your match column is E and the answer is in A, you need an array workaround:

=VLOOKUP("Apple", {E2:E7, A2:A7}, 2, FALSE)

XLOOKUP doesn't care which side the result is on:

=XLOOKUP("Apple", E2:E7, A2:A7)

Keep both ranges the same size, as here (rows 2 to 7 in each).

Step 4: Return a whole row or several columns

Google's notes say that if result_range spans more than one column (or row), XLOOKUP returns the entire row (or column) at the matching position. So to pull columns B through E for Apple in one formula:

=XLOOKUP("Apple", A2:A, B2:E)

Leave empty cells to the right of the formula for the extra values. The help page doesn't go further into how these multi-cell results behave, so check the output on your sheet.

Step 5: Show your own text when there's no match

By default a miss returns #N/A. Put your own value in the fourth argument instead:

=XLOOKUP(B24, A2:A, E2:E, "Not found")

You can use an empty string "" if you'd rather show a blank.

A note on Google's wording: the page's opening line says XLOOKUP returns the closest match when no match is found. But the argument list says match_mode defaults to 0 (exact) and missing_value defaults to #N/A. Going by the argument list, a default formula gives #N/A on a miss, and you only get a closest match if you ask for one in Step 6. Test it on your own data.

Step 6: Get the closest match instead

Set match_mode (the fifth argument) to 1 or -1. You must fill the fourth argument to reach the fifth; use "" or your not-found text.

=XLOOKUP(B24, A2:A, E2:E, "", 1)
=XLOOKUP(B24, A2:A, E2:E, "", -1)

• 1 returns the exact match, or the next value greater than search_key.
• -1 returns the exact match, or the next value lesser than search_key.

This suits things like tiered rates, where you want the nearest band rather than an error.

For partial text matches, match_mode 2 turns on wildcard matching:

=XLOOKUP(B24, A2:A, E2:E, "", 2)

Google's page doesn't spell out which wildcard characters it accepts, so try your pattern on a few rows first.

Step 7: Find the last match, not the first

If a name appears several times, such as a customer with many orders, XLOOKUP normally returns the first one. Set search_mode (the sixth argument) to -1 to search from the bottom up:

=XLOOKUP(B24, A2:A, E2:E, "Not found", 0, -1)

The 0 keeps exact matching. If you want to find duplicates rather than pick one, see how to highlight duplicates in Google Sheets.

Fixing #N/A and wrong results

Work through these in order:

1. No exact match exists. The default match_mode is exact, so the key must match the cell's contents. Add a missing_value so misses are obvious, or switch to 1 or -1 if a near value is fine.
2. Ranges don't line up. lookup_range and result_range should be the same size. A2:A7 with E2:E10 is a mismatch.
3. Lookup range is more than one column. lookup_range must be a single row or column. A2:B won't work as a lookup range.
4. Binary search on unsorted data. search_mode 2 needs the range sorted ascending first, and -2 needs it sorted descending. If your data isn't sorted, use 1 or -1.
5. You copied the BigQuery example. Formulas like table_name!fruit come from the BigQuery block and won't fit a normal sheet.

If an edit breaks a sheet full of formulas, you can restore an earlier version of the sheet.

Limits to know about

It doesn't say anything about the mobile apps either, so check there before you build a workflow around them. The one documented restriction is in BigQuery, where search_mode isn't supported. If your lookup data lives in another file, pull it in first with IMPORTRANGE and point XLOOKUP at the imported range.