Your order sheet lists product codes, and each needs its price from a list on another sheet. Enter =XLOOKUP(C2,Prices!$A$2:$A$7,Prices!$D$2:$D$7) in the price column: Excel finds the code from C2 in the Prices sheet’s code column and returns the price from the same row. The general pattern is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]), and only the first three parts are required.
XLOOKUP is in Microsoft 365 (Windows and Mac), Excel 2024, 2021, the web and mobile apps, not in 2019 or 2016.
Set up the Prices and Orders sheets
Every formula below uses one small, fictional workbook, so you can rebuild it in a couple of minutes and check each result. A sheet named Prices holds the product list in A1:D7:
| Row | A: Item ID | B: Product | C: Category | D: Price |
|---|---|---|---|---|
| 2 | P-101 | Desk Lamp | Lighting | 34.99 |
| 3 | P-102 | Office Chair | Furniture | 189.00 |
| 4 | P-103 | Monitor Arm | Accessories | 79.50 |
| 5 | P-104 | Standing Desk | Furniture | 449.00 |
| 6 | P-105 | Webcam | Electronics | 64.95 |
| 7 | P-106 | Keyboard | Electronics | 49.99 |
A sheet named Orders holds what customers bought, in A1:D8, with an empty Price column in E:
| Row | A: Order | B: Customer | C: Item ID | D: Qty |
|---|---|---|---|---|
| 2 | 1001 | Alex Rivera | P-101 | 2 |
| 3 | 1002 | Jordan Lee | P-105 | 1 |
| 4 | 1003 | Alex Rivera | P-106 | 1 |
| 5 | 1004 | Priya Shah | P-102 | 4 |
| 6 | 1005 | Jordan Lee | P-101 | 1 |
| 7 | 1006 | Alex Rivera | P-103 | 2 |
| 8 | 1007 | Priya Shah | P-110 | 1 |
Notice order 1007: P-110 isn’t on the price list. That row is there on purpose, because real order sheets always contain a code someone mistyped.
Build the price lookup
Start with a test next to the price list, where you can see every range at once, and then move the formula to the order sheet.
- Type a test code. On the Prices sheet, type P-104 in G2.
- Start the formula in H2. Type
=XLOOKUP(. A tooltip under the cell lists the six arguments, with the one you’re filling in shown in bold. - Point at what to find. Click G2 and type a comma.
- Point at where to look. Drag over A2:A7 (the codes, without the header) and type a comma.
- Point at what to return. Drag over D2:D7, the prices. This range must cover the same rows as the one before it.
- Close and confirm. Type
)and press Enter. H2 shows 449, the Standing Desk price.

Change G2 to P-102 and H2 switches to 189. XLOOKUP returns the value, not the source cell’s formatting, so apply Currency from the Home tab if the result should look like money.
Now do it for real on the order sheet:
- Click E2 on the Orders sheet and type
=XLOOKUP(C2,. - Click the Prices tab, select A2:A7 and press F4 to lock it as
$A$2:$A$7. Type a comma. - Select D2:D7 on the same sheet, press F4 again, type
)and press Enter. Excel returns you to Orders with the finished formula:=XLOOKUP(C2,Prices!$A$2:$A$7,Prices!$D$2:$D$7). - Drag the fill handle down to E8. Each row looks up its own code, while the locked ranges stay pointed at the price list.
E2 shows 34.99, E5 shows 189, and E8 shows #N/A because P-110 doesn’t exist. The next sections fix that. If the cell shows the formula itself instead of a number, here’s why Excel shows formulas instead of results and how to switch it back.
If the price list is an Excel table (Ctrl+T) named Products, the formula can use column names instead, such as =XLOOKUP(C2,Products[Item ID],Products[Price]), and it keeps covering new rows as the table grows.
When to fill in each of the six arguments
| Argument | Fill it in when | Values |
|---|---|---|
| lookup_value | Always: the code, name or number you have | A cell, text in quotes, or a number |
| lookup_array | Always: the one column (or row) to search | For example Prices!A2:A7 |
| return_array | Always: the column or columns holding the answer | Same height as lookup_array |
| if_not_found | A missing code should show a message, not #N/A | Text, a number, a cell, or another formula |
| match_mode | You need tiers, bands or partial text | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcards |
| search_mode | You want the last match, or a sorted-list search | 1 top down (default), -1 bottom up, 2 and -2 binary search on sorted data |
To use a later argument while skipping an earlier one, leave the gap between commas empty, as in =XLOOKUP(G2,A2:A7,D2:D7,,-1). Microsoft warns that the binary modes (2 and -2) return invalid results if the list isn’t sorted, and those wrong answers look perfectly normal, so leave search_mode alone unless you keep the list sorted. The full reference is Microsoft’s XLOOKUP function page.
Convert an existing VLOOKUP
Many people meet XLOOKUP because a VLOOKUP broke. The same price lookup written the old way is =VLOOKUP(G2,A2:D7,4,FALSE). Here’s how each part translates:
| VLOOKUP part | Becomes in XLOOKUP | Why it matters |
|---|---|---|
| G2 (what to find) | G2, unchanged | Nothing changes here |
| A2:D7 (whole table) | A2:A7 (just the search column) | The search column no longer has to be first |
| 4 (column number) | D2:D7 (the answer column itself) | Inserting a column in the table can’t point the formula at the wrong data |
| FALSE (exact match) | Nothing: exact is the default | Forgetting FALSE in VLOOKUP gives approximate matches |
Result: =XLOOKUP(G2,A2:A7,D2:D7). Because the search and return columns are separate arguments, you can also look left. =XLOOKUP("Webcam",B2:B7,A2:A7) returns P-105, even though the code column sits to the left of the product column, which VLOOKUP can’t do without rearranging the table.
Microsoft’s VLOOKUP function page itself recommends XLOOKUP as the improved version that works in any direction. Keep VLOOKUP only where the file is shared with someone on Excel 2019 or earlier; the walkthrough on how to use VLOOKUP in Excel covers that function in depth.
Finish the Orders sheet
Show Check code for P-110
Change E2 to =XLOOKUP(C2,Prices!$A$2:$A$7,Prices!$D$2:$D$7,"Check code") and fill down. Row 8 now reads Check code instead of #N/A. Avoid an empty text value (“”) as the message: it hides typos so well that a missing price looks like a free item.
Fall back to the Discontinued sheet
The fourth argument can be another lookup. If retired products live on a sheet named Discontinued with the same layout, this checks it only when Prices has no match:
=XLOOKUP(C2,Prices!$A$2:$A$7,Prices!$D$2:$D$7,XLOOKUP(C2,Discontinued!$A$2:$A$20,Discontinued!$D$2:$D$20,"Check code"))
Price an order by discount tier
For bands like discounts, shipping rates or grades, set match_mode. Put a tier table in F10:G13 on the Prices sheet: 0 gets 0%, 100 gets 5%, 250 gets 10%, 500 gets 15%. An order of 320 falls between two tiers:
=XLOOKUP(320,F10:F13,G10:G13,,-1)returns 10%, because -1 drops to the next smaller threshold (250).=XLOOKUP(320,F10:F13,G10:G13,,1)returns 15%, because 1 moves up to the next larger threshold (500).=XLOOKUP(320,F10:F13,G10:G13)returns #N/A, since there’s no exact 320.

When the table lists where each band starts (“from 250”), -1 is the one you want. When it lists where each band ends (“up to 250”), use 1. Keeping the tier list sorted smallest to largest makes it easier to read and check, even though only the binary search modes strictly require sorting.
Fill product, category and price in one go
Point return_array at several columns and one formula fills them all. With P-104 in G2, =XLOOKUP(G2,A2:A7,B2:D7) in H2 returns Standing Desk, Furniture and 449 across H2:J2.

This is called spilling: Excel fills the empty neighboring cells and draws a blue border around them. Only H2 holds the formula, so edit or delete H2 to change the result. The same trick works vertically: a range as the lookup value, such as =XLOOKUP(G2:G4,A2:A7,D2:D7), spills one price per code without any copying down. Another formula can refer to the whole spilled result with a # after the first cell: if that formula sits in K2, =SUM(K2#) totals every price it returns. Spilled results can’t sit inside an Excel table, so write those formulas in ordinary cells.
Answer questions from the Orders, Prices and Sales sheets
Find Alex’s latest order
XLOOKUP stops at the first match from the top. Setting search_mode to -1 makes it start from the bottom, which on a log sorted by date means the latest entry. On the Orders sheet, =XLOOKUP("Alex Rivera",B2:B8,C2:C8,,0,-1) returns P-103, Alex’s last order (1006). Without the last two arguments it returns P-101, the first.
Find the order where Jordan bought P-101
To find the order where Jordan Lee bought P-101:
=XLOOKUP(1,(B2:B8="Jordan Lee")*(C2:C8="P-101"),A2:A8) returns 1005.
Each comparison in parentheses produces one TRUE or FALSE per row. Multiplying them turns TRUE into 1 and FALSE into 0, so a row scores 1 only when both conditions hold, and XLOOKUP looks for that 1. Multiply in a third comparison for a third condition. This pattern is for exact matches; don’t combine it with match_mode -1 or 1.
Search product names for a word
With match_mode 2, an asterisk stands for any run of characters, a question mark for one character, and a tilde makes the next * or ? literal. =XLOOKUP("*Desk*",Prices!B2:B7,Prices!D2:D7,,2) returns 34.99 for Desk Lamp, the first product containing “Desk”. To search for text typed in a cell, join the asterisks: "*"&G2&"*". Without the 2, Excel looks for a product literally named *Desk*.
Pull West’s Q3 figure
On a sheet named Sales, A1:E4 holds quarterly sales with regions down column A (East, West, North) and Q1 to Q4 across row 1. West’s Q3 figure, 11,300, comes from a lookup inside a lookup:
=XLOOKUP("West",A2:A4,XLOOKUP("Q3",B1:E1,B2:E4))
The inner formula finds the Q3 column and returns all three of its values; the outer one picks West’s. A plain horizontal lookup works too: =XLOOKUP("Q2",B1:E1,B2:E2) searches across row 1 and returns East’s Q2 figure, so HLOOKUP isn’t needed.
Total North from Q2 to Q4
XLOOKUP returns a reference, so two of them can mark the ends of a range. North’s Q2 through Q4 total (8,020, 8,700 and 9,150 in the sample) is =SUM(XLOOKUP("Q2",B1:E1,B4:E4):XLOOKUP("Q4",B1:E1,B4:E4)), which returns 25,870. To total a whole column of looked-up results instead, see how to sum a column in Excel.
Believable numbers that are wrong
An error at least tells you something failed. These problems return a believable number instead:
- The code appears twice in the price list. XLOOKUP returns the first one and ignores the other. Check the list for repeats, and remove duplicate rows if one is stale. To see every matching row rather than one, the FILTER function is the right tool.
- A binary search_mode on unsorted data. Remove the sixth argument.
- An approximate match_mode on an exact lookup. A -1 or 1 left over from a tier formula returns the nearest code’s price instead of #N/A. Remove the fifth argument or set it to 0.
- Offset ranges. A2:A7 searched with D3:D8 returned pulls each answer from the row below. Both ranges must start on the same row.
- A 0 where you expected a blank. When the matching cell in the return column is empty, the result displays as 0. Wrap it if that’s misleading:
=IF(XLOOKUP(G2,A2:A7,C2:C7)="","",XLOOKUP(G2,A2:A7,C2:C7)). - An empty lookup cell. Microsoft notes that an omitted lookup_value makes XLOOKUP return blank cells it finds in lookup_array, so make sure the lookup cell is filled before trusting a result.
What the error in the cell is telling you
| You see | Usual cause | Fix |
|---|---|---|
| #N/A | No match: missing code, extra space, or a number stored as text on one side | Add if_not_found; clean spaces with TRIM(C2); convert text numbers so both sides match |
| #VALUE! | lookup_array and return_array are different sizes | Make both ranges start and end on the same rows |
| #SPILL! | A multi-cell result has something in its way, or sits inside a table | Clear the cells; use Select Obstructing Cells; see Microsoft’s #SPILL! error page |
| #NAME? | Misspelled function, or a version without XLOOKUP | Check spelling; open the file in Microsoft 365, 2024, 2021 or the web |
| #REF! | A spilled result linked to another workbook that is closed | Open the source workbook, or copy its data into this file |
Wildcard searches can also return #N/A when match_mode isn’t 2, because Excel treats the asterisk as a literal character.
Sending the workbook to someone on Excel 2019 or earlier
Excel 2019 and 2016 can open your file, but they can’t calculate XLOOKUP, and those cells show #NAME? once the sheet recalculates. You have three options:
- Send values. Copy the lookup column, then paste it back with Paste Values. The numbers stay; the formulas go.
- Rewrite with INDEX and MATCH.
=INDEX(Prices!$D$2:$D$7,MATCH(C2,Prices!$A$2:$A$7,0))does the same exact-match price lookup and also looks left. The 0 in MATCH means exact match. - Suggest Excel for the web. The browser version runs XLOOKUP, so the recipient can open the shared file there.
If the recipient works in Google Sheets instead, its lookup functions use similar arguments; see how to use VLOOKUP in Google Sheets.
XLOOKUP questions
Should I wrap XLOOKUP in IFERROR?
Usually not. The built-in if_not_found argument handles the missing-match case and still lets real problems, such as #VALUE! from mismatched ranges, show up. IFERROR hides every error, including the ones you’d want to see.
Is XLOOKUP better than INDEX and MATCH?
Both can look left and handle two-way lookups. XLOOKUP does it in one function with a built-in not-found message and reverse search, which most people find easier to read. INDEX and MATCH still win when the file must work in Excel 2019 or older. In current Excel, XMATCH is the modern partner to INDEX, with the same match_mode and search_mode values as XLOOKUP.
Can Copilot write the formula for me?
If your Microsoft 365 plan includes Copilot in Excel, you can describe the lookup in plain words and review the formula it suggests. Check the ranges and match settings against the argument table above before you trust it. See how to use Copilot in Excel to create a formula.
What happens to the formula if I insert a column in the price list?
The ranges shift with the data, so the formula keeps returning prices. That’s the main practical gain over a VLOOKUP with a hard-coded column number, which keeps counting to column 4 even after the price has moved to column 5.
A good next move: open a copy of a workbook that uses VLOOKUP, convert one formula with the table above, and compare the results row by row before converting the rest.

Kermit Matthews runs Live2Tech from just outside Philadelphia. He started on a university help desk, spent years as the IT person for small businesses, and has been writing tech guides full time since 2020. Each guide is checked on a device he owns, and re-checked when a menu moves. More about Kermit.