XLOOKUP From Another Sheet or Workbook
By ExcelReady TeamUpdated 5 min read
The short answer
To use XLOOKUP with another sheet, put the sheet name and an exclamation mark before the lookup and return ranges: =XLOOKUP(A2,Products!$A$2:$A$50,Products!$C$2:$C$50). If the sheet name has spaces, wrap it in single quotes, like 'Price List'!A2:A50. For another workbook, add the file name in square brackets before the sheet name.
Key takeaways
- A reference to another sheet is the sheet name, an exclamation mark, then the range: Products!A2:A50.
- Sheet names with spaces or symbols need single quotes: 'Price List'!A2:A50.
- The easiest way to build the reference is to click the other sheet while typing the formula.
- The lookup and return ranges must be the same size, or XLOOKUP returns #VALUE!.
- For another workbook, Excel adds the file name in square brackets, such as [Prices.xlsx]Products!A2:A50.
On this page+
To run XLOOKUP on another sheet, you write the same formula as usual and put the sheet name in front of each range, like Products!A2:A50. Most real workbooks keep the lookup table on its own tab, so this is how XLOOKUP is used day to day. This guide shows how to build the reference by clicking, how to handle sheet names with spaces, and how to look up from a separate workbook.
XLOOKUP needs Excel 2021, Excel 2024, Microsoft 365 or Excel for the web. If the function itself is new to you, start with the XLOOKUP guide for beginners.
How to write XLOOKUP with another sheet#
A reference to another sheet has three parts: the sheet name, an exclamation mark, and the range. Say your Orders sheet has product IDs in column A, and a Products sheet holds the price list:
| A | B | C | |
|---|---|---|---|
| 1 | ID | Product | Price |
| 2 | K-101 | Cable Ties | 3.40 |
| 3 | K-105 | Tape Gun | 7.90 |
| 4 | K-180 | Label Printer Roll | 12.50 |
| 5 | K-220 | Shipping Scale | 21.00 |
| 6 | K-301 | Bubble Wrap | 5.75 |
On the Orders sheet, in B2, type:
=XLOOKUP(A2,Products!$A$2:$A$6,Products!$C$2:$C$6)If A2 holds K-220, the result is 21.00. The lookup value, A2, has no sheet name because it is on the same sheet as the formula. Both ranges point to Products.
The $ signs lock the ranges, so you can copy the formula down the Orders sheet (Windows: Ctrl+D; Mac: ⌘D) and every row still searches the same price list.
Build the reference by clicking, not typing#
Clicking is faster than typing and avoids spelling mistakes in sheet names. Here are the steps:
- On the Orders sheet, click B2 and type
=XLOOKUP(. - Click A2, then type a comma.
- Click the Products tab at the bottom of the window. Select A2:A6 and press F4 (Mac: ⌘T) to add
$signs. Type a comma. - Still on Products, select C2:C6, press F4 again, and type
). - Press Enter. Excel takes you back to Orders and shows the result.
To switch tabs from the keyboard instead, press Ctrl+Page Down for the next sheet and Ctrl+Page Up for the previous one (Mac: ⌥→ and ⌥←). This is handy in workbooks with many tabs.
Don't click back to the Orders tab before pressing Enter. If you do, Excel adds that sheet's cells to the formula. For more on how sheet references work in general, see how to reference another sheet in Excel.
Sheet names with spaces need single quotes#
When a sheet name has a space, punctuation, or starts with a number, Excel wraps it in single quotes:
=XLOOKUP(A2,'Price List'!$A$2:$A$6,'Price List'!$C$2:$C$6)The quotes go around the sheet name only, and the exclamation mark sits outside them. If you leave the quotes out, Excel can't read the formula and shows an error message. Clicking to build the reference adds the quotes for you, which is one more reason to click.
If you rename a sheet later, Excel updates every formula that refers to it. You don't need to edit them.
Use an Excel table so you don't need sheet names#
If the price list is an Excel table, you can refer to its columns by name. Select the list and choose Insert > Table (Windows shortcut: Ctrl+T) to make a table, then name it Products on the Table Design tab. The formula becomes:
=XLOOKUP(A2,Products[ID],Products[Price])Table names work from any sheet, so there is no sheet name in the formula at all. The table also grows when you add new products, so the lookup always covers every row.
XLOOKUP from another workbook#
To look up from a different file, open both workbooks and click into the other file while building the formula, just like with another sheet. Excel adds the file name in square brackets:
=XLOOKUP(A2,[Prices.xlsx]Products!$A$2:$A$6,[Prices.xlsx]Products!$C$2:$C$6)When you close Prices.xlsx, Excel changes the reference to include the full folder path, such as 'C:\Shared\[Prices.xlsx]Products'!$A$2:$A$6. XLOOKUP keeps showing the last saved values from the closed file.
Links to other workbooks are fragile. If someone moves or renames the file, the formula can't find it, and Excel asks you to update links when you open the workbook. If you can, copy the lookup table into the same workbook on its own tab instead.
Common errors and how to fix them#
Most cross-sheet XLOOKUP problems come from one of these:
| Problem | Likely cause | Fix |
|---|---|---|
| #VALUE! | Lookup and return ranges are different sizes | Make both cover the same rows, e.g. A2:A6 and C2:C6 |
| #N/A | The ID isn't in the list, or has extra spaces | Check the data, or add a message with if_not_found |
| #REF! | The other sheet was deleted | Restore the sheet or point the formula at the new one |
| Wrong rows after copying | Ranges not locked with $ | Add $ signs with F4 (Mac: ⌘T) |
| Error dialog when entering | Missing single quotes around a sheet name with spaces | Write 'Price List'!A2:A6 |
To replace #N/A with a clear message, add a fourth argument, as shown in XLOOKUP if not found. If you need the same lookup to work in older Excel, compare the options in INDEX MATCH vs XLOOKUP. For a full example of a lookup tab feeding a report, see the employee lookup sheet walkthrough, and keep the XLOOKUP function guide handy for the syntax.
Practise it#
The formula is the same whether the table is next to you or on another tab, so practise the lookup first. Practise XLOOKUP in a live spreadsheet: you type the formula and it checks the result instantly. Then practise working across worksheets to get comfortable with sheet references, quotes and clicking between tabs.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
In this sheet, the price list sits in D1:E6 so you can see it. In B2, write an XLOOKUP that finds the product ID in A2 in D2:D6 and returns the unit price from E2:E6. On a real workbook, the ranges would just have a sheet name in front.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Unit Price | Price | ||||
| 2 | K-220 | |||||
| 3 | K-105 | |||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 |
Tip: Three parts: the ID to find (A2), where to look (D2:D6), and what to return (E2:E6).
Practise this in a lesson
Frequently asked questions
How do I use XLOOKUP across two sheets?
Type =XLOOKUP( and click the lookup value on the current sheet. Then click the other sheet's tab and select the lookup range, type a comma, select the return range, and press Enter. Excel adds the sheet name for you.
Why does my XLOOKUP from another sheet return #VALUE!?
The lookup range and the return range are different sizes, for example A2:A50 and C2:C40. Make both ranges cover the same rows.
Can XLOOKUP look up from a closed workbook?
Yes. Once the link is set, XLOOKUP shows the values saved in the other file and refreshes them when you update links. SUMIF and COUNTIF are different: they need the source workbook open.
Do I need quotes around the sheet name in XLOOKUP?
Only when the name has spaces, punctuation or starts with a number. Then wrap it in single quotes, like 'Price List'!A2:A50. Excel adds them for you when you click to build the reference.