XLOOKUP Function in Excel and Google Sheets
Purpose
Search a column for a value and return data from a corresponding range — the modern replacement for VLOOKUP
Return value
The value or range of values from return_array at the position matching lookup_value in lookup_array
Every time I start writing a VLOOKUP these days I stop and switch to XLOOKUP. XLOOKUP separates the search column and the return range into two completely independent arguments — you point at what you are searching in and you point separately at what you want back. Those two things can be anywhere. Any column to the left. Any column to the right. Multiple columns at once. No column number to count. No IFERROR wrapper needed because there is a built-in fourth argument for missing values. Exact match is the default so you never forget to add FALSE. XLOOKUP is the better function in every measurable way. The only reason to use VLOOKUP over XLOOKUP is file compatibility with Excel 2019 or earlier. For everything else, XLOOKUP is the right choice.
Syntax
✓ Excel 2021+
✓ Google Sheets
= Same syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Available in Excel 2021 and Excel 365 only. Not available in Excel 2019 or earlier. Available in all versions of Google Sheets.
Arguments
| Argument | Required | Description |
|---|---|---|
| lookup_value | Required | The value to search for. Can be a cell reference, typed value, or formula result. Data types must match the lookup column. |
| lookup_array | Required | The single column or row to search in. Unlike VLOOKUP this is just the search column, not the entire table. |
| return_array | Required | The column or range to return values from. Can be anywhere — left, right, or another sheet. Span multiple columns to return multiple values at once. |
| if_not_found | Optional | What to return when no match is found. Use "" for blank or "Not found" for a message. Without this XLOOKUP returns #N/A when no match exists. |
| match_mode | Optional | 0 for exact match (default). -1 for exact or next smaller. 1 for exact or next larger. 2 for wildcard match. Most lookups use 0. |
| search_mode | Optional | 1 to search first to last (default). -1 to search last to first for the most recent match among duplicates. |
How it works
XLOOKUP searches lookup_array from top to bottom for lookup_value and returns the value from return_array at the same row position. Both lookup_array and return_array are fully independent references — they can be in any order or direction. When return_array spans multiple columns, XLOOKUP spills all values into adjacent cells automatically. Make sure those adjacent cells are empty or the spill fails with #SPILL!. Always include the if_not_found argument to avoid #N/A in shared reports. The match_mode argument defaults to 0 for exact match — only change this for approximate or wildcard matching. Set search_mode to -1 to find the most recent match when values are duplicated in the lookup column.
Examples
1
Look up a product price by code — column to the right of the search column.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Lookup Code | Code | Product Name | Price |
| 2 | PRD-003 | PRD-001 | Wireless Mouse | 29.99 |
| 3 | PRD-002 | USB Keyboard | 45.99 | |
| 4 | PRD-003 | Monitor Stand | 89.50 | |
| 5 | PRD-004 | Laptop Bag | 59.99 | |
| 6 | PRD-005 | HDMI Cable | 12.99 | |
| 7 | PRD-006 | Webcam HD | 79.00 | |
| 8 | PRD-007 | Desk Lamp | 34.50 |
Row 2: 89.50
— PRD-003 found in D2:D8 and 89.50 returned from the matching position in F2:F8.
No column number to specify — you point directly at the return column. Inserting a column between D and F will never break this formula unlike VLOOKUP.
2
Return name AND price in one formula using a multi-column return array.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Lookup Code | Code | Name | Price |
| 2 | PRD-005 | PRD-001 | Wireless Mouse | 29.99 |
| 3 | PRD-002 | USB Keyboard | 45.99 | |
| 4 | PRD-003 | Monitor Stand | 89.50 | |
| 5 | PRD-004 | Laptop Bag | 59.99 | |
| 6 | PRD-005 | HDMI Cable | 12.99 | |
| 7 | PRD-006 | Webcam HD | 79.00 |
Row 2: HDMI Cable
— PRD-005 found — Name (HDMI Cable) spills into the result cell and Price (12.99) spills into the adjacent cell.
Setting return_array to E2:F7 spans two columns. XLOOKUP spills both values automatically, replacing two separate VLOOKUP formulas with one.
3
Find the most recent clock-in time for an employee by searching from the bottom up.
| A | B | C | |
|---|---|---|---|
| 1 | Find | Employee | Clock-in |
| 2 | EMP-002 | EMP-001 | 08:02 |
| 3 | EMP-003 | 07:45 | |
| 4 | EMP-002 | 09:10 | |
| 5 | EMP-001 | 08:55 | |
| 6 | EMP-002 | 08:30 | |
| 7 | EMP-004 | 07:58 | |
| 8 | EMP-002 | 10:15 | |
| 9 | EMP-001 | 07:40 |
Row 2: 10:15
— EMP-002 appears three times. Searching from bottom to top returns 10:15 — the last entry in the data.
search_mode -1 tells XLOOKUP to start from the last row and work upward. The first match going up is the most recent entry.
Common use cases
1. Look up a salary from a column to the LEFT of the employee ID — a lookup direction VLOOKUP cannot do
=XLOOKUP(A2,C:C,B:B,"Not found")
2. Return a product name, price, and stock level simultaneously from one formula using a multi-column return array
=XLOOKUP(A2,D:D,E:G)
3. Find the most recent clock-in or status update for each employee when records are stored chronologically
=XLOOKUP(A2,B:B,C:C,"No record",0,-1)
4. Retrieve a discount rate using approximate matching on a sorted customer tier table
=XLOOKUP(A2,Tiers!A:A,Tiers!B:B,0,-1)
5. Perform a wildcard lookup when the search value may contain extra characters or variations
=XLOOKUP("*"&A2&"*",D:D,E:E,"Not found",,2)
Common errors
#N/A — lookup value not found and no if_not_found specified
The lookup value does not exist in lookup_array and the fourth argument was not provided, so XLOOKUP returns its default #N/A error.
Fix: Always include the fourth argument in formulas that will be shared or put in reports: =XLOOKUP(A2,D:D,E:E,"Not found"). Use "" to return a blank cell.
#VALUE! — lookup_array and return_array different sizes
When searching a column, lookup_array and return_array must cover the same number of rows. If they are different sizes, XLOOKUP cannot align the search results with the return values.
Fix: Use full column references for both — D:D and E:E always cover the same number of rows. Or use matched ranges: D2:D100 and E2:E100.
#SPILL! — adjacent cells not empty
When return_array spans multiple columns, XLOOKUP tries to spill results into the cells to the right of the formula. If any of those cells contain data, the spill fails.
Fix: Clear all cells in the spill range to the right of the XLOOKUP formula. Or narrow return_array to a single column if you only need one value.
Tips and variations
Always include the if_not_found argument in production formulas
The fourth argument replaces the need for IFERROR entirely and makes the formula shorter and cleaner. =XLOOKUP(A2,D:D,E:E,"Not found") is more readable than =IFERROR(XLOOKUP(A2,D:D,E:E),"Not found"). Use "" for a blank cell or any message that makes the not-found case self-explanatory to the reader.
=XLOOKUP(A2,D:D,E:E,"Not found")
Return multiple columns of data in one formula to replace several VLOOKUPs
Set return_array to span multiple adjacent columns and XLOOKUP spills all values automatically. This is cleaner than writing a separate lookup for each column of data you need to retrieve and ensures all columns always return data from the same matching row.
=XLOOKUP(A2,D:D,E:H)
Use search_mode -1 to always find the most recent duplicate entry
When lookup_array contains the same value multiple times and you want the most recent one, set the sixth argument to -1. XLOOKUP searches from the bottom of the column upward and returns the first match it finds — which is the last entry chronologically in a top-to-bottom dataset.
=XLOOKUP(A2,B:B,C:C,"None",0,-1)
Excel vs Google Sheets
Excel vs Google Sheets
XLOOKUP works identically in Excel 2021, Excel 365, and Google Sheets. Same syntax, same behaviour, same defaults. Formulas copy between applications without changes. Not available in Excel 2019 or earlier — use INDEX MATCH for those versions.
Related reading
Frequently asked questions
XLOOKUP is the modern replacement for VLOOKUP introduced in Excel 2021 and Excel 365. It is better in every measurable way. It uses two separate arguments for the search column and return column instead of one combined table with a column number, so inserting or deleting columns never breaks the formula. It can look in any direction — left, right, up, or down — unlike VLOOKUP which can only look right. It defaults to exact match so you do not need to add FALSE at the end. It has a built-in argument for the not-found case so you do not need to wrap in IFERROR. And it can return multiple columns simultaneously in a single formula.
XLOOKUP is only available in Excel 2021 and Excel 365 subscription. It is not available in Excel 2019, Excel 2016, Excel 2013, or any earlier version. If your file must be compatible with older Excel versions, use INDEX MATCH instead — it achieves the same results and works back to Excel 2003. In Google Sheets, XLOOKUP is available in all current versions.
Yes — this is one of XLOOKUP's most powerful features. Set return_array to span multiple columns and XLOOKUP automatically spills results into adjacent cells. =XLOOKUP(A2,D:D,E:G) finds the match in column D and returns values from columns E, F, and G simultaneously. This replaces writing three separate VLOOKUP formulas and the results update together whenever the lookup value changes.
The fourth argument specifies what to return when the lookup value is not found instead of the default #N/A error. =XLOOKUP(A2,D:D,E:E,"Not found") returns the text Not found instead of an error when A2 is not in column D. This built-in handling replaces the need to wrap XLOOKUP in IFERROR, making the formula shorter and cleaner. Use "" to return a blank cell or 0 to return zero.
Set the sixth argument search_mode to -1. This tells XLOOKUP to search from the last row upward rather than the first row downward. The first match it finds going up is the most recent entry in the data. =XLOOKUP(A2,B:B,C:C,"None",0,-1) finds the last occurrence of A2 in column B and returns the corresponding value from column C. This is particularly useful for finding the latest transaction, most recent status, or newest record for each customer or employee.