XLOOKUP in Google Sheets searches one row or column for a value and returns whatever sits in the same position in another range, written as =XLOOKUP(search_key, lookup_range, result_range). Only those three arguments are required; the optional missing_value, match_mode and search_mode default to #N/A, an exact match and a first-to-last search.
Here's the catch most people miss. One XLOOKUP replaces both VLOOKUP and HLOOKUP, and it can return a value that sits to the left of the column you're searching. Google's own example is =XLOOKUP("Apple", E2:E7, A2:A7). To do the same with VLOOKUP, you'd have to build an array on the fly with curly braces: =VLOOKUP("Apple", {E2:E7, A2:A7}, 2, FALSE).
The six XLOOKUP arguments
| Argument | Required? | What it does |
|---|---|---|
| search_key | Yes | The value to search for, such as 42, "Cats" or B24 |
| lookup_range | Yes | Where to search. It must be a single row or a single column |
| result_range | Yes | Where the answer comes from. Its row or column size should match lookup_range |
| missing_value | Optional, #N/A by default | What to return if there's no match |
| match_mode | Optional, 0 by default | How to decide what counts as a match |
| search_mode | Optional, 1 by default | Which direction, or which method, to search in |
Match mode and search mode values
| Value | match_mode means | search_mode means |
|---|---|---|
| 0 | Exact match (the default) | Not used |
| 1 | Exact match, or the next value greater than search_key | Search from the first entry to the last (the default) |
| -1 | Exact match, or the next value lesser than search_key | Search from the last entry to the first |
| 2 | Wildcard match | Binary search; sort the range in ascending order first |
| -2 | Not used | Binary search; sort the range in descending order first |
Search mode -1 is the quiet hero here. If a value appears more than once, searching from the last entry to the first gives you the most recent one in a list that's added to over time, without any sorting.
Writing your first XLOOKUP
- Click the cell where you want the answer.
- Type =XLOOKUP( and the value to find, either in quotes like "Apple" or as a cell reference.
- Add the lookup range, a single column such as A2:A.
- Add the result range, the column holding the answer, such as E2:E.
- If you want something friendlier than #N/A, add a fourth argument with the text to show instead.
- Close the bracket and press Enter.
That gives you =XLOOKUP("Apple", A2:A, E2:E), which Google lists as the straight replacement for =VLOOKUP("Apple", A2:E, 5, FALSE). Notice there's no column number to count and no TRUE or FALSE to remember.
Swapping old VLOOKUP and HLOOKUP formulas
| Old formula | XLOOKUP version |
|---|---|
| =VLOOKUP("Apple", A2:E, 5, FALSE) | =XLOOKUP("Apple", A2:A, E2:E) |
| =HLOOKUP("Price", A1:E6, 6, FALSE) | =XLOOKUP("Price", A1:E1, A6:E6) |
| =VLOOKUP("Apple", {E2:E7, A2:A7}, 2, FALSE) | =XLOOKUP("Apple", E2:E7, A2:A7) |
Watch the defaults when you swap. VLOOKUP and HLOOKUP default to an approximate match if you leave out is_sorted, and Google recommends FALSE for an exact match. XLOOKUP starts from an exact match, so an old formula that relied on the approximate default needs match_mode 1 or -1 to behave the same way.
If result_range is more than one row or column, XLOOKUP returns the entire row or column at the position where it found the match, so one formula can pull back a whole record.
Missing matches and errors
With VLOOKUP, Google's fix for a search key that doesn't exist is to wrap the formula: =IFNA(VLOOKUP(G3, B4:D8, 3, FALSE),"NOT FOUND"). XLOOKUP does the same job inside the formula through missing_value, which keeps things shorter and easier to read.
Binary search, search mode 2 or -2, is only safe when the range is sorted first, ascending or descending to match. And for wildcards, Google's VLOOKUP help lists a question mark for any single character and an asterisk for any sequence of characters; in XLOOKUP, wildcard matching is match_mode 2. If you're still on the older function, my guide to VLOOKUP in Google Sheets walks through it step by step.
When another lookup function still fits
| Function | How it searches | Watch out for |
|---|---|---|
| VLOOKUP | Down the first column of a range | Approximate match is the default; wildcards need is_sorted set to FALSE |
| HLOOKUP | Across the first row of a range | No regular expressions or wildcards; an index outside the range's rows returns #VALUE! |
| LOOKUP | Through a sorted row or column | Only works if the data is sorted; a missing key uses the next smaller value |
| INDEX with MATCH | Any direction | MATCH needs 0 for unsorted data, and returns #N/A! on a two-dimensional range |
| XLOOKUP | Any single row or column, first to last or last to first | lookup_range can't be more than one row or column |
LOOKUP's fallback is worth knowing. If the data contains 1, 3 and 5 and you search for 2, Google says LOOKUP uses 1. INDEX and MATCH is the classic two-way lookup: =INDEX(A1:D6, MATCH("Mary", A1:A6, 0), 2) returns Vegetarian in Google's guest list example, and swapping the hardcoded 2 for a second MATCH on the header row makes both the row and column dynamic. And if you only need the position rather than the value, MATCH on its own returns it, as in =MATCH("Sunday",A2:A9,0).
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
So which lookup should you use?
I'd write every new lookup in Sheets with XLOOKUP. It's shorter, it doesn't care which side the answer sits on, and missing_value means you don't need an IFNA wrapper. I'd keep INDEX and MATCH for when you want the column picked by header name, and leave LOOKUP alone unless your data's sorted. If you also work in Microsoft's app, here's how XLOOKUP in Excel compares.
Frequently asked questions
Does Google Sheets have XLOOKUP?
Yes. The syntax is XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode), and only the first three arguments are required.
Can XLOOKUP look to the left in Google Sheets?
Yes. For example, =XLOOKUP("Apple", E2:E7, A2:A7) returns a value from column A while searching column E.
What does XLOOKUP return if there's no match?
It returns #N/A by default, or whatever you set as the missing_value argument.
How do I make XLOOKUP search from the bottom up?
Set search_mode to -1, which searches from the last entry to the first.
Can XLOOKUP return more than one column?
Yes. If result_range is more than one row or column, it returns the entire row or column at the position where the match was found.
