XLOOKUP Basic Example in Excel and Google Sheets
Purpose
XLOOKUP is the formula I now reach for every time I used to write VLOOKUP. Microsoft released it in 2021…
XLOOKUP is the formula I now reach for every time I used to write VLOOKUP. Microsoft released it in 2021 and it fixes every frustration I had with VLOOKUP — no more counting columns, no more breaking when someone inserts a column, no more being stuck searching left to right only. XLOOKUP is simpler to write, easier to read, and genuinely more powerful. The three core arguments are all you need for most lookups: what you are searching for, where to search for it, and what column to return. That is it. No column index number to get wrong. Google Sheets added XLOOKUP shortly after and the syntax is identical, so any formula you write in one app works in the other without changes. If you are still writing VLOOKUP out of habit, this is the post that will change that.
Syntax
✓ Excel 2021+
✓ Google Sheets
= Same syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Arguments
| Argument | Required | Description |
|---|---|---|
| lookup_value | Required | The value you are searching for. Can be a cell reference, a typed value, or a formula result. Data types must match the lookup column — numbers do not match text versions of the same number. |
| lookup_array | Required | The column or row to search in. This is a single column or row, not an entire table. XLOOKUP searches this range for your lookup_value. |
| return_array | Required | The column or range to return values from. Can be to the left or right of lookup_array, on another sheet, or even span multiple columns to return several values at once. |
| if_not_found | Optional | What to return when no match is found. Use an empty string to return a blank, a word like Not found, or any value. Without this argument XLOOKUP returns #N/A when the value is missing. |
| 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 from first to last (default). -1 to search from last to first which is useful for finding the most recent match. |
How it works
XLOOKUP separates the lookup column and the return column into two distinct arguments rather than combining them into one table range the way VLOOKUP does. It searches your lookup_array from top to bottom looking for a match to your lookup_value. When it finds a match it returns the value at the same position in your return_array. Because the return_array is independent, it can be any column anywhere in your spreadsheet — to the left, to the right, or even on a different sheet. If no match is found XLOOKUP returns a #N/A error by default, but you can add a fourth argument to specify exactly what to return instead of the error. The fifth argument controls match mode — exact match is the default so you do not have to specify FALSE the way you did with VLOOKUP. This alone eliminates one of the most common VLOOKUP mistakes I see people make.
Examples
1
Look up the price of each product by searching the product code column and returning from the price column.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Lookup Code | Product Code | Product Name | Price | Result |
| 2 | PRD-003 | PRD-001 | Wireless Mouse | 29.99 | =XLOOKUP(A2,B2:B11,D2:D11,"Not found") |
| 3 | PRD-002 | USB Keyboard | 45.99 | ||
| 4 | PRD-003 | Monitor Stand | 89.50 | 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 | ||
| 9 | PRD-008 | USB Hub | 27.99 | ||
| 10 | PRD-009 | Mouse Pad XL | 19.99 | ||
| 11 | PRD-010 | Headset Pro | 129.00 |
Row 2: 89.50
— PRD-003 was found in the product code column and 89.50 was returned from the price column.
Unlike VLOOKUP, there is no column number to specify. You point directly at the column you want returned. This also means inserting columns between lookup and return columns will never break this formula.
2
Return a product name and the category together using a multi-column return array in a single formula.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Lookup Code | Product Code | Product Name | Category | Name Result | Category Result |
| 2 | PRD-005 | PRD-001 | Wireless Mouse | Peripherals | =XLOOKUP(A2,B2:B11,C2:D11) | |
| 3 | PRD-002 | USB Keyboard | Peripherals | |||
| 4 | PRD-003 | Monitor Stand | Accessories | |||
| 5 | PRD-004 | Laptop Bag | Accessories | |||
| 6 | PRD-005 | HDMI Cable | Cables | HDMI Cable | Cables | |
| 7 | PRD-006 | Webcam HD | Peripherals | |||
| 8 | PRD-007 | Desk Lamp | Accessories | |||
| 9 | PRD-008 | USB Hub | Cables | |||
| 10 | PRD-009 | Mouse Pad XL | Accessories | |||
| 11 | PRD-010 | Headset Pro | Peripherals |
Row 2: HDMI Cable
— PRD-005 was found and both the Name and Category columns were returned simultaneously.
By setting the return_array to two columns (E2:F11), XLOOKUP spills both values into adjacent cells E2 and F2 automatically. This is something VLOOKUP cannot do without writing two separate formulas.
3
Find the last time a specific employee clocked in by searching from the bottom of the log upward.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Find Employee | Employee | Clock-in | Last Login |
| 2 | EMP-002 | EMP-001 | 08:02 | =XLOOKUP(A2,B2:B11,C2:C11,"No record",0,-1) |
| 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-003 | 09:22 | ||
| 9 | EMP-002 | 10:15 | 10:15 | |
| 10 | EMP-001 | 07:40 | ||
| 11 | EMP-004 | 08:05 |
Row 2: 10:15
— EMP-002 appears three times. Searching from last to first returns 10:15 — the most recent clock-in.
Setting search_mode to -1 tells XLOOKUP to start from the bottom and work upward. The first match it finds going upward is the last one in the list — perfect for finding the most recent entry.
Common use cases
1. Look up an employee salary from the salary column regardless of whether it is left or right of the ID column
=XLOOKUP(A2,HR!B:B,HR!F:F,"Not found")
2. Return a product name and price simultaneously by using a multi-column return array
=XLOOKUP(A2,Products!A:A,Products!B:C)
3. Find the most recent transaction for a customer by searching from bottom to top
=XLOOKUP(A2,TXN!A:A,TXN!D:D,"None",,,-1)
4. Retrieve a tax rate using approximate match for a rate table sorted by income bracket
=XLOOKUP(A2,Rates!A:A,Rates!B:B,,1)
5. Look up a value with a wildcard when the code contains extra characters
=XLOOKUP("*"&A2&"*",Codes!A:A,Codes!B:B,"Not found",,2)
Common errors
#N/A
The lookup_value was not found in the lookup_array. This happens when the value genuinely does not exist, when there is a data type mismatch, or when there are extra spaces in the values.
Fix: Use the if_not_found argument to return a friendly message instead of the error. Check for data type mismatches using ISNUMBER. Use TRIM to remove extra spaces.
#VALUE!
The lookup_array and return_array are different sizes. XLOOKUP requires them to have the same number of rows when searching a column.
Fix: Make sure both arrays cover exactly the same rows. If lookup_array is D2:D100 then return_array should also be 99 rows such as F2:F100.
Spill error (#SPILL!)
When return_array covers multiple columns, XLOOKUP tries to spill results into adjacent cells. If those cells are not empty, the formula cannot spill and shows #SPILL!.
Fix: Clear the cells to the right of the formula result so XLOOKUP has space to spill. Or reduce the return_array to a single column if you only need one value.
Tips and variations
Use the if_not_found argument instead of IFERROR
The fourth argument of XLOOKUP is a cleaner alternative to wrapping the formula in IFERROR. Instead of =IFERROR(XLOOKUP(...), "Not found") you can write =XLOOKUP(...,,...,"Not found"). This makes the formula shorter and easier to read, especially when you have complex lookup and return arrays.
=XLOOKUP(A2,D:D,F:F,"Not found")
Return multiple columns in one formula
Set the return_array to span multiple columns and XLOOKUP returns all of them at once, spilling into adjacent cells automatically. This replaces writing separate XLOOKUP formulas for each column and is one of the biggest productivity gains XLOOKUP offers.
=XLOOKUP(A2,D:D,E:G)
Search from last to first to get the most recent match
When you have duplicate values and want the most recent one, set search_mode to -1. XLOOKUP searches from the bottom of the list upward and returns the first match it finds which is the last entry in your data.
=XLOOKUP(A2,D:D,F:F,"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 argument behaviour, same default exact match. Formulas copy between the two applications without changes. The main caveat is availability — XLOOKUP does not exist in Excel 2019 or earlier. If you need to share files with colleagues on older Excel versions, use INDEX MATCH instead which works all the way back to Excel 2003.
Related reading
Frequently asked questions
XLOOKUP is the modern replacement for VLOOKUP and fixes all of its major limitations. XLOOKUP can look left or right, does not need a column number, returns exact match by default, handles missing values with a built-in if_not_found argument, and supports wildcard and approximate matches. VLOOKUP is only available for compatibility. If you are on Excel 365 or a recent version of Google Sheets, XLOOKUP is the better choice every time.
XLOOKUP is only available in Excel 2021 and Excel 365. It is not available in Excel 2019, 2016, or earlier. In Google Sheets it is available in all versions. If you need to share a workbook with users on older Excel versions, use INDEX MATCH instead which works in all versions.
Yes — this is one of XLOOKUP's best features. Set the return_array to cover multiple columns and XLOOKUP spills results across as many columns as you specify. For example =XLOOKUP(A2,IDcol,B:D) returns values from columns B, C and D for the matching row all at once.
match_mode controls how XLOOKUP searches. 0 means exact match which is the default. -1 means exact match or next smaller value. 1 means exact match or next larger value. 2 means wildcard match where you can use * and ? in your lookup value. The default exact match is what most people need.
XLOOKUP has a built-in if_not_found argument — the sixth argument — that lets you specify what to return when no match exists. Write =XLOOKUP(A2,range,return,"Not found") and it returns Not found instead of #N/A. This is much cleaner than wrapping the whole formula in IFERROR.