To use XLOOKUP in Excel, click the cell where the answer should go, type a formula such as =XLOOKUP(F2,A2:A6,B2:B6) and press Enter. On our practice sheet it looked for item ID 103 in column A and returned Pad from column B. XLOOKUP finds an exact match by default, so three parts are all a normal lookup needs: the value to find, the range to search and the range to return, which you swap for your own cells.
Three optional parts handle the rest. The fourth replaces #N/A with your own text, the fifth changes how close a match has to be, and the sixth makes Excel search from the bottom up. Unlike VLOOKUP, XLOOKUP can return a value from a column to the left of the one it searches, and one formula can bring back two columns at once.
The section on errors shows the ones we caused on purpose, including two kinds of #SPILL! that Excel labels differently.
The short version
- The formula: type =XLOOKUP(F2,A2:A6,B2:B6) with your own cells for the value to find, the range to search and the range to return, then press Enter.
- Exact match is the default: there is no FALSE or 0 to remember for a normal lookup.
- Nothing found: add "Not found" as the fourth part to show that instead of #N/A.
- Two columns at once: make the return range two columns wide and keep the cells to the right empty.
- Insert it from a dialog: press Shift + F3, search for XLOOKUP and fill in the boxes.
- Errors: #NAME? usually means a typo, and #SPILL! usually means the cells it needs are filled or merged.
XLOOKUP formulas at a glance
| What you want | Formula we used | What it returned |
|---|---|---|
| An item name from its ID | =XLOOKUP(F2,A2:A6,B2:B6) | Pad for ID 103 |
| A value from a column to the left | =XLOOKUP("Pad",B2:B6,A2:A6) | 103 |
| Two columns at once | =XLOOKUP(F2,A2:A6,B2:C6) | Pad and Office, side by side |
| Your own text when nothing matches | =XLOOKUP(F3,A2:A6,B2:B6,"Not found") | Not found for ID 999 |
| The next smaller value | =XLOOKUP(F4,D2:D6,B2:B6,"Not found",-1) | Pad, priced 5, for a price of 7 |
| A partial text match | =XLOOKUP(F5,B2:B6,D2:D6,"Not found",2) | 8 for Mu* |
| The last match instead of the first | =XLOOKUP(F6,B2:B6,D2:D6,"Not found",0,-1) | 12 for Mug |
| A column picked by its heading | =XLOOKUP(F2,A2:A6,XLOOKUP("Dept",B1:D1,B2:D6)) | Office |
| A total between two items | =SUM(XLOOKUP(102,A2:A6,D2:D6):XLOOKUP(104,A2:A6,D2:D6)) | 25 |
What the six parts of XLOOKUP mean
| Part | What it does | In our first formula |
|---|---|---|
| lookup_value | The value to find. It can be a cell, a number or text in double quotes | F2, which holds 103 |
| lookup_array | The column or row to search | A2:A6, the item IDs |
| return_array | The column or columns to bring the answer back from | B2:B6, the item names |
| if_not_found | Optional. Text to show when there is no match, instead of #N/A | Left out |
| match_mode | Optional. 0 is an exact match and the default, -1 and 1 accept the next smaller or next larger value, 2 turns on wildcards | Left out, so exact |
| search_mode | Optional. 1 searches from the first item and is the default, -1 from the last, and 2 and -2 run a binary search on sorted data | Left out, so top to bottom |
Only the first three parts are required. Microsoft writes the syntax as =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]), with square brackets around the optional parts. Excel shows the same line under the cell while you type.
Where VLOOKUP needs a whole table and a column number, XLOOKUP takes the column to search and the column to return as two separate ranges. That is why it can return a value from either side of the search column.
Keep the search range and the return range on the same rows. Ours both run from row 2 to row 6, so the third ID in A2:A6 lines up with the third item in B2:B6.
Enter your first XLOOKUP formula
Our practice sheet holds item IDs in A2:A6, item names in column B, departments in column C and prices in column D, with headings in row 1. The ID to look up, 103, sits in F2, and the answer goes in G2. If Excel itself is new to you, start with our guide to Excel for beginners, which covers creating, saving and reopening a workbook.
Click G2 and type =XLO. Excel listed XLOOKUP with the tip Searches a range or an array for a match and returns the corresponding item from a second range or array, followed by a note that an exact match is used by default. Press Tab and Excel writes =XLOOKUP( for you, with a ScreenTip under the cell that lists all six parts and shows the one you are on in bold.
Click F2 and type a comma, drag over A2:A6 and type another comma, then drag over B2:B6. Type the closing parenthesis and press Enter. G2 showed Pad, the item on the row where 103 sits, and the formula bar showed =XLOOKUP(F2,A2:A6,B2:B6).
The formula reads F2 each time it calculates, so typing another ID there changes the answer. A drop-down list of item IDs in F2 lets people pick an ID from the list instead of typing one.
If the suggestion list gets in the way, press Esc, the key Microsoft lists for closing it. Alt + Down Arrow also closed it on our PC, but Microsoft lists those keys for turning Formula AutoComplete on or off. If the list stops appearing, press them again, or open File > Options > Formulas and check that Formula AutoComplete is ticked, as it was on ours.
Use Insert Function instead of typing
If you would rather fill in boxes, click the answer cell and press Shift + F3, or select Insert Function at the left end of the Formulas tab. Type XLOOKUP in the search box and select Go. On our PC the category changed to Recommended, with XLOOKUP as the only match.
Select XLOOKUP and then OK to open Function Arguments, which has one box per part and a help line for the box you are in. We filled it with F2, A2:A6 and D2:D6 to fetch a price instead of a name, and the Formula result line showed 5 before we clicked OK. The If_not_found and Match_mode boxes can stay empty, and Search_mode sits below them, so scroll the box list to reach it.
Shift + F3 is one of the Excel keyboard shortcuts worth learning if you write formulas often. In Excel for the web the same keys open a simpler Insert function dialog, covered further down.
Return a value from a column to the left
VLOOKUP cannot return a value from a column to the left of the one it searches. XLOOKUP has no such limit, and Microsoft's help page says it returns a result from the same row regardless of which side the return column is on.
To find the ID of the item called Pad, we typed =XLOOKUP("Pad",B2:B6,A2:A6) in G4. It searched the Item column and returned 103 from the Item ID column to its left. Text typed straight into the formula needs double quotes, while a cell reference such as F2 does not.
Return two columns with one formula
Make the return range more than one column wide and XLOOKUP brings back each column in it. In G5 we entered =XLOOKUP(F2,A2:A6,B2:C6), and Excel filled G5 with Pad and H5 with Office, the item and the department for ID 103.
Excel calls this spilling. Only G5 holds the formula, and while G5 was selected a blue outline ran around both cells. The cells it spills into must be empty and unmerged, or the formula shows #SPILL! instead, which the section on errors explains.
Show your own message instead of #N/A
When XLOOKUP finds no match, it shows #N/A. Our list has no item 999, and =XLOOKUP(F3,A2:A6,B2:B6) in G10 returned #N/A with a warning button beside the cell.
Put your own text in the fourth part to replace it. With =XLOOKUP(F3,A2:A6,B2:B6,"Not found"), the same cell showed Not found. To leave the cell looking empty instead, use two double quotes with nothing between them as the fourth part.
The fourth part only steps in when there is no match. Because XLOOKUP has it built in, you do not need to wrap the formula in IFERROR or IFNA, and a misspelled function name or a broken range still shows its own error where you can see it.
Find the next smaller or next larger value
The fifth part, match_mode, decides how close a match has to be. Leave it out, or use 0, for an exact match. Microsoft defines -1 as an exact match or else the next smaller item, and 1 as an exact match or else the next larger item.
We looked up a price of 7 with =XLOOKUP(F4,D2:D6,B2:B6,"Not found",-1) in G7. No item costs exactly 7, so XLOOKUP took the next smaller price, 5, and returned Pad. We kept "Not found" as the fourth part, which Microsoft says only shows when no valid match is found.
Our prices were in no particular order, 2, 8, 5, 12 and 3, and XLOOKUP still picked the right one. That makes -1 a simple way to look up bands such as discounts or shipping rates: list the value where each band starts, and the lookup returns the band a number falls into.
Match partial text or find the last match
XLOOKUP only treats an asterisk as a wildcard when the fifth part is 2. The default, 0, is a plain exact match, so Mu* would only find a cell that really contains Mu*. Microsoft names the asterisk, the question mark and the tilde as the characters with a special meaning in mode 2.
With Mu* in F5, =XLOOKUP(F5,B2:B6,D2:D6,"Not found",2) in G8 returned 8. Two items start with Mu, both called Mug, and XLOOKUP returned the price of the first one, the Kitchen mug in row 3.
That is the default search order at work, because XLOOKUP starts at the top and stops at the first match. To get the last match instead, put -1 in the sixth part, search_mode.
Our =XLOOKUP(F6,B2:B6,D2:D6,"Not found",0,-1) in G9 searched for Mug from the bottom up and returned 12, the price of the Gifts mug in row 5. The 0 before it keeps the match exact. Before relying on the first or last of several matches, check whether you meant to remove duplicate rows in Excel instead.
Search modes 2 and -2 run a binary search. Microsoft says they rely on the search range being sorted in ascending or descending order, and that unsorted data returns invalid results, so leave them alone unless your list is sorted that way.
Combine XLOOKUP with other formulas
Because XLOOKUP can return a whole column, one XLOOKUP can choose the return range for another. Our =XLOOKUP(F2,A2:A6,XLOOKUP("Dept",B1:D1,B2:D6)) in K2 returned Office. The inner XLOOKUP found the heading Dept in B1:D1 and returned that column of B2:D6, and the outer one looked up ID 103 in it.
Because the inner lookup searches the headings, the column can be chosen by typing a heading instead of by editing ranges. Microsoft's own examples use this pattern to match a row and a column at the same time.
XLOOKUP can also mark one end of a range. =SUM(XLOOKUP(102,A2:A6,D2:D6):XLOOKUP(104,A2:A6,D2:D6)) in K3 returned 25, the prices of items 102, 103 and 104 added together.
To see why it works, select the cell and open Formulas > Evaluate Formula. After one press of Evaluate, the first XLOOKUP had turned into the cell reference $D$3, so SUM was adding a range that starts at D3. When you already know which cells to add, the simpler way to sum a column in Excel is quicker.
Use XLOOKUP in Excel for the web
XLOOKUP works the same way in a browser. In Excel for the web in Microsoft Edge, typing =XLO listed XLOOKUP with a shorter description, Searches a value to return an item from a range. Pressing Tab opened a larger card than the desktop ScreenTip, with a description, an example formula and one line explaining each part.
On a small list with IDs in A2:A3 and 103 in F2, =XLOOKUP(F2,A2:A3,B2:B3) returned Pad, just as on the desktop. Shift + F3 opened a simpler Insert function dialog, with Next and Insert with preview buttons where the desktop one has OK. Microsoft says the Formula AutoComplete on and off setting does not apply to Excel for the web.
Fix XLOOKUP errors
Each XLOOKUP error points at a different cause. The table sums up what each one means and how to clear it, and the sections under it show what happened when we caused each error on our sheet.
| What you see | Usual cause | Fix |
|---|---|---|
| #N/A | The value is not in the search range | Check the value, or add a fourth part such as "Not found" |
| #NAME? | The function name is misspelled | Correct the spelling |
| #SPILL! with Spill range isn't blank | A cell the result needs already holds something | Use Select Obstructing Cells, then clear or move that entry |
| #SPILL! with Spill range has merged cell | A merged cell sits where the result needs to go | Unmerge the cells or move the formula |
| #SPILL! from a formula inside an Excel table | Microsoft says spilled formulas are not supported in Excel tables | Move the formula out of the table, or convert the table to a range |
| A message that the cell is on a protected sheet | The sheet is protected | Review > Unprotect Sheet, with the password if one was set |
| The formula itself instead of a result | Show Formulas is on, or the cell was formatted as Text | Turn off Formulas > Show Formulas, or press Ctrl + `. For a Text cell, set it to General, then press F2 and Enter |
#N/A means there was no match
Our missing ID 999 showed #N/A until we added the fourth part, as shown earlier. If you can see the value in the list and still get #N/A, the two may not be identical, and the wider checklist to fix an Excel lookup that is not working covers stray spaces and numbers stored as text.
#NAME? usually means a typo
We typed XLOKUP with one O missing, and the cell showed #NAME? with a warning button beside it. Correcting the name to XLOOKUP returned Pad. Microsoft says a typo in the function name is the top reason for this error, so check the spelling before anything else.
If XLOOKUP is missing from the suggestion list as you type, that is a different problem. The section on versions below explains which Excel apps have it.
#SPILL! means the result has nowhere to go
A result that fills more than one cell needs those cells free. We typed BLOCK into H11 and entered the two-column formula in G11, which showed #SPILL! with a dashed outline over G11:H11, the cells it wanted. The warning button beside the cell opened a menu headed Spill range isn't blank, with Select Obstructing Cells to jump to the cell in the way.
Clearing H11 fixed it, and G11 and H11 showed Pad and Office. Microsoft's advice is the same, to delete or move whatever is in the outlined cells. Its list of causes also includes a formula inside an Excel table, where it suggests moving the formula out of the table or converting the table to a range.
Merged cells cause a different #SPILL! message. With H15 and I15 merged and empty, the same formula in G15 showed #SPILL!, and the menu heading read Spill range has merged cell.
After Unmerge Cells, the formula returned Pad and Office. If the merge has to stay, Microsoft suggests moving the formula to cells clear of merged ones, and our guide shows how to unmerge cells in Excel when the merge was only there for looks.
A protected sheet blocks the formula
On a sheet protected with Review > Protect Sheet, typing in a cell brought up a message that the cell or chart you're trying to change is on a protected sheet, and that you might be asked for a password. After Review > Unprotect Sheet, the same XLOOKUP returned Pad. If the sheet has a password, Excel asks for it, and Microsoft says it cannot retrieve a forgotten one.
If the workbook belongs to someone else, ask its owner. Microsoft describes protection as two steps, unlocking the cells others may edit and then protecting the sheet, so an owner can leave the lookup cells open, and our guide to unprotect an Excel worksheet covers the rest.
The formula shows as text
If whole columns show formulas instead of results, Show Formulas is probably on. When we selected Formulas > Show Formulas, column G showed each XLOOKUP as text, and selecting it again brought the results back. Ctrl + `, the grave accent key, did the same.
If only one cell shows its formula, the cell may have been formatted as Text before you typed. Set its format to General, then press F2 and Enter so Excel reads the formula again, and the steps to fix Excel formulas showing as text cover that and the rarer causes. For anything that still will not calculate, try the guide to fix Excel formulas that are not working.
Which versions of Excel have XLOOKUP
Microsoft lists XLOOKUP for Excel for Microsoft 365, Excel 2024 and Excel 2021 on Windows and Mac, and for Excel on iPad, iPhone and Android. It also worked for us in Excel for the web, as shown above.
Microsoft says XLOOKUP is not available in Excel 2016 and Excel 2019. If you share workbooks with people on those versions, rebuild the lookup and use VLOOKUP in Excel instead, which those versions have. If you have Microsoft 365 and XLOOKUP does not appear as you type, update Microsoft Office and try again.
How we tested this guide
We tested this in Excel for Microsoft 365 on Windows 11, version 25H2, and Excel for the web in Edge, typing the worked examples into a sheet of made-up items.
Frequently Asked Questions
What are the three required XLOOKUP arguments?
The value to find, the range to search and the range to return, in that order. In =XLOOKUP(F2,A2:A6,B2:B6), F2 holds the ID, A2:A6 is searched and B2:B6 supplies the answer.
Does XLOOKUP use exact match by default?
Yes. Leave the fifth part out, or use 0, and XLOOKUP only returns an exact match, showing #N/A when there is none.
How do I get the last match when there are duplicates?
Put -1 in the sixth part, search_mode, so XLOOKUP searches from the bottom up. On our list it returned the last Mug's price, 12, where the default search returned the first one's, 8.
Why does my XLOOKUP show #N/A?
The value was not found in the search range. Check that it really is there with no extra spaces, or add a fourth part such as "Not found" to show your own text instead.
Why do I get #SPILL! from XLOOKUP?
Usually the result needs more than one cell and one of them is filled or merged. Select the error, read the heading in the warning button's menu, then clear the cell in the way or unmerge the cells. If the formula sits inside an Excel table, Microsoft says to move it out of the table or convert the table to a range.
Why does XLOOKUP show #NAME? or not appear in my Excel?
A misspelled function name shows #NAME?, and one missing letter gave us that error straight away, so check the spelling first. If XLOOKUP does not appear at all, check your version, because Microsoft says Excel 2016 and Excel 2019 do not have it.
Can I use XLOOKUP in Excel for the web?
Yes. The same lookup on a shorter range returned Pad in Excel for the web in Edge. Typing =XLO listed XLOOKUP, and pressing Tab opened a help card explaining each part.