How to Use XLOOKUP in Google Sheets
Nov 08, 2024
Quick answer
Yes, Google Sheets has XLOOKUP. The syntax is =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). It searches in any direction, defaults to an exact match, and lets you return your own text instead of #N/A when nothing is found. Nothing needs enabling.
Last updated: August 20, 2026
XLOOKUP replaces VLOOKUP, HLOOKUP and most of what people used INDEX and MATCH for. It searches up, down, left or right, it defaults to an exact match instead of an approximate one, and it has a built in answer for when nothing is found.
This guide covers whether Sheets supports it, the full syntax including every match mode and search mode, seven worked examples, how to look up across sheets and files, and what to do when it returns an error.
Get our free Excel formulas cheat sheet
Plus new tutorials and template drops. Enter your email and we'll send it over.
Table of Contents
- Does Google Sheets have XLOOKUP?
- What is XLOOKUP and why use it over VLOOKUP?
- What is the XLOOKUP syntax in Google Sheets?
- What does each match mode do?
- What does each search mode do?
- Seven worked XLOOKUP examples
- How do you XLOOKUP from another sheet or another file?
- Why is your XLOOKUP not working?
- Where is Google's official XLOOKUP documentation?
- Frequently asked questions
Does Google Sheets have XLOOKUP?
Yes. Google added XLOOKUP to Sheets in 2022 and it is available to every account, personal and Workspace alike. There is no add-on to install, no lab to opt into, and no setting to switch on. Start typing =XL in any cell and the autocomplete will offer it.
Two things do occasionally make people think it is missing:
- A stale browser tab. If a spreadsheet has been open since before you last updated, reload the page.
- An Excel file that has not been converted. An .xlsx opened in Sheets works fine, but if you copy a formula out of desktop Excel the argument separators may not match your locale. See the troubleshooting table below.
The rule: if =XLOOKUP( does not autocomplete for you, the problem is the file or the locale, not your account.
What is XLOOKUP and why use it over VLOOKUP?
XLOOKUP searches for a value in one range and returns the value in the same position from a second range. That is the whole idea. Because the lookup range and the result range are separate arguments rather than a column number inside one block, the two can sit anywhere relative to each other, which is what makes left-facing and horizontal lookups possible without rearranging your data.
| Capability | XLOOKUP | VLOOKUP | INDEX and MATCH |
|---|---|---|---|
| Return a value to the left of the key | Yes | No | Yes |
| Horizontal lookup | Yes, same function | No, you need HLOOKUP | Yes |
| Default match type | Exact | Approximate, you must pass FALSE | Approximate, you must pass 0 |
| Built in value when nothing matches | Yes, the missing_value argument | No, wrap it in IFERROR | No, wrap it in IFERROR |
| Survives an inserted column | Yes | No, the column index silently breaks | Yes |
| Search from the bottom up | Yes, search_mode -1 | No | Only with a workaround |
The rule: the single biggest reason to move off VLOOKUP is not the leftward lookup, it is the column index. VLOOKUP's third argument is a number, so inserting a column into your data changes the answer without changing the formula and without showing an error. XLOOKUP has no column index to break.
What is the XLOOKUP syntax in Google Sheets?
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
| Argument | Required | What it does |
|---|---|---|
search_key |
Yes | The value you are looking for. |
lookup_range |
Yes | Where to search. Must be a single row or a single column, never a block. |
result_range |
Yes | Where to take the answer from. Must be the same size as lookup_range. |
missing_value |
No | What to return when nothing matches. Defaults to #N/A. |
match_mode |
No | How strictly to match. Defaults to 0, exact. |
search_mode |
No | Which end to start from. Defaults to 1, first to last. |
What does each match mode do?
| Value | Behaviour | Use it for |
|---|---|---|
| 0 (default) | Exact match only. | IDs, names, SKUs. Nearly every real lookup. |
| 1 | Exact, otherwise the next larger value. | Shipping bands where you round up to the next tier. |
| -1 | Exact, otherwise the next smaller value. | Tax brackets, volume discounts, commission tiers. |
| 2 | Wildcard match. * is any run of characters, ? is one character, ~ escapes the next one. |
Partial names, messy imported text. |
What does each search mode do?
| Value | Behaviour | Use it for |
|---|---|---|
| 1 (default) | Search first to last. | The normal case. |
| -1 | Search last to first. | Duplicate keys where you want the most recent entry, not the oldest. |
| 2 | Binary search, ascending. | Very large lists already sorted A to Z or low to high. |
| -2 | Binary search, descending. | Very large lists already sorted in reverse. |
The rule: binary search does not check whether your data is sorted. Point it at unsorted data and it returns a confidently wrong answer with no error. Only use modes 2 and -2 when you know the range is sorted.
Seven worked XLOOKUP examples
Example 1: a basic vertical lookup
You have a list of employees and their departments, and you want one person's department.
Task: find the department for "Charlie".
Formula: =XLOOKUP("Charlie", A2:A5, B2:B5)
search_key: "Charlie", the name being looked up.lookup_range: A2:A5, the employee names.result_range: B2:B5, the departments.
XLOOKUP finds "Charlie" in A2:A5 and returns the value in the same position from B2:B5, which is HR.
Example 2: a custom message instead of #N/A
By default a failed lookup returns #N/A, which looks like a broken spreadsheet. The fourth argument replaces it with whatever you want.
Task: look up Eve's department, and return "Employee Not Found" if she is not on the list.
Formula: =XLOOKUP("Eve", A2:A5, B2:B5, "Employee Not Found")
Eve is not in A2:A5, so instead of an error the cell reads "Employee Not Found". This is the argument that makes IFERROR wrappers unnecessary.
Example 3: an approximate match for tiered pricing
You have spending thresholds and matching discount percentages, and you need the discount for an amount that falls between two thresholds.
Task: find the discount for a spend of 750.
Formula: =XLOOKUP(750, A2:A5, B2:B5, "No tier", -1)
A match_mode of -1 means exact, or the next value down. There is no 750 in the list, so XLOOKUP drops to 500, the largest threshold that does not exceed 750, and returns its discount of 10%.
Note the "No tier" in the fourth position. The optional arguments are positional, so if you want to set match_mode you have to supply something in the missing_value slot first. Filling it with a real value is clearer than leaving an empty comma.
Example 4: a horizontal lookup
When your data runs across a row instead of down a column, XLOOKUP handles it with no change of function. This is the job VLOOKUP cannot do at all.
Task: find the score for "Science".
Formula: =XLOOKUP("Science", B1:D1, B2:D2)
lookup_range: B1:D1, the header row of subject names.result_range: B2:D2, the row of scores.
The function finds Science in B1:D1 and returns 92 from the matching position in B2:D2.
Example 5: a lookup on two criteria at once
XLOOKUP has no built in multi-criteria mode, so you build the criteria into the lookup range itself using ARRAYFORMULA and boolean logic.
Task: find the stock level for a Shirt that is Blue.
Formula: =ARRAYFORMULA(XLOOKUP(1, (A2:A5="Shirt")*(B2:B5="Blue"), C2:C5))
(A2:A5="Shirt")and(B2:B5="Blue")each produce an array of TRUE and FALSE, one entry per row.- Multiplying the two arrays turns them into 1 where both tests pass and 0 everywhere else.
- XLOOKUP then searches that array of ones and zeroes for the value 1, and returns the stock level from C2:C5 in the same position.
- ARRAYFORMULA is what allows the comparison to run across the whole range rather than a single cell.
The result is 45. Add a third criterion by multiplying in another bracketed test the same way.
Example 6: a reverse lookup
Returning a value that sits to the left of the key is the classic reason people abandon VLOOKUP. In XLOOKUP it needs no special handling at all: just swap which range goes where.
Task: find the item code for "Monitor".
Formula: =XLOOKUP("Monitor", B2:B5, A2:A5)
The lookup range is column B and the result range is column A, to its left. XLOOKUP does not care about the order, and returns A103.
Example 7: a partial match with wildcards
You have full names and want to find someone by surname alone.
Task: find the email for a name containing "Brown".
Formula: =XLOOKUP("*"&"Brown"&"*", A2:A5, B2:B5, "Not Found", 2)
"*"&"Brown"&"*"builds the pattern*Brown*, matching any cell that contains Brown anywhere in the text.- The
2at the end is thematch_mode. Without it the asterisks are treated as literal characters and the lookup fails. "Not Found"keeps the cell readable if nothing matches.
In practice you would reference a cell rather than typing the name, for example ="*"&D1&"*", so the search term is editable.
The formula finds "Alice Brown" and returns her email address.
How do you XLOOKUP from another sheet or another file?
Another tab in the same file
Put the tab name and an exclamation mark in front of each range:
=XLOOKUP(A2, Employees!A2:A, Employees!B2:B, "Not found")
If the tab name contains a space, wrap it in single quotes: 'Raw Data'!A2:A. Both ranges have to point at the same sheet and be the same length.
A different spreadsheet file
Wrap each range in IMPORTRANGE:
=XLOOKUP(A2, IMPORTRANGE("spreadsheet_url", "Employees!A2:A"), IMPORTRANGE("spreadsheet_url", "Employees!B2:B"), "Not found")
Do this first or it will not work. IMPORTRANGE needs a one-time permission grant, and it can only ask for it when it is on its own. Put a bare =IMPORTRANGE("spreadsheet_url", "Employees!A1") in any spare cell, click Allow access when the prompt appears, then delete that cell and build your XLOOKUP. Nesting IMPORTRANGE inside XLOOKUP before the connection is approved just returns an error with no prompt, which is where most people get stuck.
Why is your XLOOKUP not working?
| Symptom | Cause | Fix |
|---|---|---|
#N/A |
No match. Usually a trailing space, or a number stored as text on one side and as a number on the other. | Check with =EXACT(A2, D2). Wrap the key in TRIM(). Add a missing_value so the sheet reads cleanly either way. |
#VALUE! |
lookup_range and result_range are different sizes, or lookup_range is a block rather than a single line. | Google's reference is explicit: lookup_range must be a singular row or column, and result_range must match its size. |
| It returns the wrong row, with no error | match_mode 1 or -1, or search_mode 2 or -2, running on unsorted data. | Sort the lookup range, or go back to match_mode 0 and search_mode 1. |
| It returns the oldest duplicate, you wanted the newest | search_mode defaults to 1, first to last. | Set search_mode to -1 to search from the bottom up. |
| Wildcards are matched literally | match_mode is not set to 2. | Add the fifth argument: =XLOOKUP("*"&A1&"*", B:B, C:C, "Not found", 2). |
| The result is 0 when it should be blank | The matched cell is genuinely empty, and Sheets renders an empty referenced cell as 0. | Wrap it: =IF(XLOOKUP(A2,B:B,C:C)="", "", XLOOKUP(A2,B:B,C:C)). |
| Sheets says the formula parse failed | Argument separators do not match your file's locale. Locales that use a comma as the decimal separator use semicolons between arguments. | Try =XLOOKUP(A2; B2:B5; C2:C5), or change the locale under File > Settings. |
| The IMPORTRANGE version errors | The connection between the two files has never been approved. | Run a bare IMPORTRANGE in a spare cell first and click Allow access. See the section above. |
Where is Google's official XLOOKUP documentation?
Google's reference page is XLOOKUP function, in the Google Docs Editors Help centre. It is the authoritative source for the parameter definitions, and it confirms two details worth knowing: missing_value returns #N/A by default, and lookup_range must be a single row or column.
What it deliberately does not include is worked examples for tiered pricing, multi-criteria lookups, cross-file lookups, or what to do when the formula silently returns the wrong row. That is what the rest of this page is for. Read the official page for the definitions and come back here for the cases.
Final thoughts
XLOOKUP is the only lookup function most people in Google Sheets now need. It does everything VLOOKUP and HLOOKUP do, in either direction, with an exact match by default and a sensible answer when nothing is found. The two habits worth forming are always filling in the missing_value argument, and leaving match_mode at 0 unless you have a specific tiered reason not to.
For more easy-to-follow spreadsheet guides and the latest Excel templates, visit Simple Sheets and the related articles below.
Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials.
Frequently asked questions
Does Google Sheets have XLOOKUP?
Yes. Google added XLOOKUP to Sheets in 2022 and it is available on every account, personal and Workspace, with nothing to install or enable. Type =XL in a cell and it appears in the autocomplete list.
What is the XLOOKUP syntax in Google Sheets?
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). The first three are required. missing_value defaults to #N/A, match_mode defaults to 0 for an exact match, and search_mode defaults to 1 for first to last.
How is XLOOKUP different from VLOOKUP?
XLOOKUP takes the lookup range and the result range as separate arguments instead of a column number, so it can return values to the left of the key and can search across a row as well as down a column. It also defaults to an exact match and has a built in value for when nothing is found.
Why is my XLOOKUP returning #N/A?
Nothing in the lookup range matches the search key exactly. The usual causes are a trailing space or a number stored as text on one side of the comparison. Test with =EXACT(), wrap the key in TRIM(), and fill in the missing_value argument so a genuine no-match reads as text rather than an error.
Can XLOOKUP pull data from another spreadsheet?
Yes, by wrapping each range in IMPORTRANGE. You have to approve the connection between the two files first by running a bare IMPORTRANGE in a spare cell and clicking Allow access. Nesting it inside XLOOKUP before approval returns an error without ever showing the prompt.
Can XLOOKUP match on two criteria?
Yes, but not with a built in argument. Multiply the conditions together to build a lookup array, as in =ARRAYFORMULA(XLOOKUP(1, (A2:A="Shirt")*(B2:B="Blue"), C2:C)), then search that array for the value 1.
Related Articles
Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
