Look Up Prices From Another Sheet With XLOOKUP in Excel

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.

  1. Type a test code. On the Prices sheet, type P-104 in G2.
  2. 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.
  3. Point at what to find. Click G2 and type a comma.
  4. Point at where to look. Drag over A2:A7 (the codes, without the header) and type a comma.
  5. Point at what to return. Drag over D2:D7, the prices. This range must cover the same rows as the one before it.
  6. Close and confirm. Type ) and press Enter. H2 shows 449, the Standing Desk price.
Excel worksheet with a product table and the formula =XLOOKUP(G2,A2:A7,D2:D7) being typed in cell H2, with colored outlines on the lookup value, lookup column and return column
(1) The lookup value in G2, (2) the column Excel searches, and (3) the column the answer comes from, each outlined as you type the formula. (Illustration)

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:

  1. Click E2 on the Orders sheet and type =XLOOKUP(C2,.
  2. Click the Prices tab, select A2:A7 and press F4 to lock it as $A$2:$A$7. Type a comma.
  3. 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).
  4. 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.
Diagram of a discount tier table showing that an order of 320 matches the 250 tier with match_mode -1 and the 500 tier with match_mode 1
(1) With match_mode -1, an order of 320 falls back to the 250 tier; (2) with match_mode 1, it moves up to the 500 tier. (Illustration)

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.

Excel worksheet showing one XLOOKUP formula in cell H2 that returns the product name, category and price across H2 to J2 with a blue spill border
(1) One formula in H2 with a three-column return array (2) spills the product, category and price into H2:J2. (Illustration)

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:

  1. Send values. Copy the lookup column, then paste it back with Paste Values. The numbers stay; the formulas go.
  2. 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.
  3. 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.