How to Use XLOOKUP in Excel and Fix Errors

Type =XLOOKUP(F2,A2:A6,B2:B6) with your own cells and press Enter. Examples for left lookups, two columns, wildcards and #N/A or #SPILL! fixes.

Contents

Don't Miss the Good Stuff

Get tech news that matters delivered to your inbox.

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 wantFormula we usedWhat 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

PartWhat it doesIn our first formula
lookup_valueThe value to find. It can be a cell, a number or text in double quotesF2, which holds 103
lookup_arrayThe column or row to searchA2:A6, the item IDs
return_arrayThe column or columns to bring the answer back fromB2:B6, the item names
if_not_foundOptional. Text to show when there is no match, instead of #N/ALeft out
match_modeOptional. 0 is an exact match and the default, -1 and 1 accept the next smaller or next larger value, 2 turns on wildcardsLeft out, so exact
search_modeOptional. 1 searches from the first item and is the default, -1 from the last, and 2 and -2 run a binary search on sorted dataLeft 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.

Excel sheet with an XLOOKUP formula in G2 returning Pad for item ID 103.
Click to expand
The first formula, =XLOOKUP(F2,A2:A6,B2:B6). F2 holds the ID to find, 103 (1), Excel searches the Item ID column, A2:A6 (2), and returns from the Item column, B2:B6 (3), so G2 shows Pad (4).

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.

Excel Formula AutoComplete listing XLOOKUP, then the XLOOKUP argument ScreenTip under the cell.
Click to expand
Typing =XL in K5 (1) lists XLOOKUP (2), and Tab inserts it. Its tip says it searches a range or an array for a match and returns the corresponding item from a second range or array, and that an exact match is used by default (3). After Tab, here in G2, the ScreenTip lists all six parts with the current one in bold (4).

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.

Excel Insert Function dialog finding XLOOKUP, then Function Arguments filled with F2, A2:A6 and D2:D6.
Click to expand
In Insert Function, search for XLOOKUP (1), select it as the only match (2) and read what it does (3). Function Arguments has one box per part (4), and Formula result shows 5 before you click OK (5). Here the dialog fetched the price for ID 103 into G3.

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.

Excel XLOOKUP returning item ID 103 from a column to the left of the Item column.
Click to expand
=XLOOKUP("Pad",B2:B6,A2:A6) in G4 (1) searches the Item column (2) and returns from the Item ID column on its left (3), giving 103 (4).

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 XLOOKUP with a two-column return range spilling Pad and Office into two cells.
Click to expand
One formula in G5 (1) with a two-column return range, B2:C6 (2), spills Pad and Office into G5 and H5 (3).

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.

Two Excel sheets comparing XLOOKUP returning #N/A and returning Not found for ID 999.
Click to expand
ID 999 is not in the list (1). With no fourth part (2), G10 shows #N/A (3). With "Not found" as the fourth part (4), the same cell shows that message instead (5).

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.

Excel XLOOKUP with match mode -1 returning Pad for a price of 7.
Click to expand
With match_mode -1 (1), a price of 7 (2) has no exact match, so XLOOKUP takes the next smaller price, 5 (3), and returns Pad (4).

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.

Excel sheet with XLOOKUP wildcard and reverse search results for two Mug rows.
Click to expand
With wildcard mode 2, Mu* (1) returns 8 in G8, the price of the first Mug (2). With search_mode -1 (5), Mug (3) returns 12 in G9, the price of the last Mug (4).

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.

Excel nested XLOOKUP returning Office, a SUM of two XLOOKUPs returning 25, and Evaluate Formula.
Click to expand
A nested XLOOKUP (1) returns Office, the department of ID 103 (2). A SUM between two XLOOKUPs (3) returns 25, the prices of items 102 to 104 (4). Evaluate Formula shows the first XLOOKUP turned into the reference $D$3 (5).

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.

Excel for the web help card for XLOOKUP with its syntax, an example and one line per argument.
Click to expand
In Excel for the web, pressing Tab after =XLO writes =XLOOKUP( (1) and opens a card with the syntax, the current part in green (2), an example (3) and one line per part (4).

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.

Excel for the web listing XLOOKUP as you type, then a lookup on a short range returning Pad.
Click to expand
In Excel for the web, =XLO lists XLOOKUP with a shorter description (1). The same lookup on a shorter range, =XLOOKUP(F2,A2:A3,B2:B3) (2), returns Pad (3).

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 seeUsual causeFix
#N/AThe value is not in the search rangeCheck the value, or add a fourth part such as "Not found"
#NAME?The function name is misspelledCorrect the spelling
#SPILL! with Spill range isn't blankA cell the result needs already holds somethingUse Select Obstructing Cells, then clear or move that entry
#SPILL! with Spill range has merged cellA merged cell sits where the result needs to goUnmerge the cells or move the formula
#SPILL! from a formula inside an Excel tableMicrosoft says spilled formulas are not supported in Excel tablesMove the formula out of the table, or convert the table to a range
A message that the cell is on a protected sheetThe sheet is protectedReview > Unprotect Sheet, with the password if one was set
The formula itself instead of a resultShow Formulas is on, or the cell was formatted as TextTurn 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.

Excel cell showing #NAME? because the formula misspells XLOOKUP as XLOKUP.
Click to expand
XLOKUP, with one O missing (1), shows #NAME? (2). Spelled XLOOKUP, the same formula returned Pad.

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.

Two Excel #SPILL! error menus, one for a blocked cell and one for a merged cell.
Click to expand
Top: the result needs G11:H11 but H11 holds BLOCK (1). The warning button (2) opens a menu headed Spill range isn't blank (3) with Select Obstructing Cells (4). Bottom: the result needs G15:H15 but H15 is merged (5), and the heading reads Spill range has merged cell (6).

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.

Excel protected sheet message with the Unprotect Sheet button on the Review tab.
Click to expand
Typing on a protected sheet brings up this message (1). Review > Unprotect Sheet (2) turns protection off, with the password if one was set.

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.

Excel Formulas tab with Show Formulas on and XLOOKUP formulas shown in column G.
Click to expand
On the Formulas tab (1), Show Formulas (2) makes column G show each XLOOKUP formula instead of its result (3). Select it again, or press Ctrl + `, to switch back.

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.

Share