You want to look up values from several worksheets in a single Excel workbook, but building the formula manually takes too long. XLOOKUP is a powerful function, but combining it across multiple sheets usually requires nested IFERROR or complex array formulas. Copilot in Excel can write these formulas for you using natural language prompts. This article explains how Copilot interprets multi-sheet XLOOKUP requests, the prerequisites you need, and the exact steps to generate the formula without errors.
Key Takeaways: Copilot + XLOOKUP Across Sheets
- Copilot pane > Data analysis > Ask a question: Describe the lookup in plain English, such as “Look up price from the Price sheet and quantity from the Quantity sheet”.
- Table names or named ranges: Copilot works best when your data is formatted as Excel tables or has defined names for each sheet’s range.
- Manual verification of generated formulas: Always test the XLOOKUP result on a small dataset before applying it to hundreds of rows.
How Copilot in Excel Handles XLOOKUP Across Multiple Sheets
Copilot in Excel can generate formulas that reference data from different worksheets in the same workbook. When you ask for an XLOOKUP that spans multiple sheets, Copilot builds a formula that either uses separate XLOOKUP calls for each sheet or combines them with functions like IFERROR or VSTACK. The key requirement is that your data on each sheet is structured as a table. Excel tables have names like Table1, Table2, or custom names you assign. Copilot uses these table names to construct the correct sheet and range references.
Behind the scenes, Copilot analyzes the column headers and data types in each table. It then suggests a formula that matches your natural language prompt. For example, if you say “Find the product name from Sheet1 and the price from Sheet2 using the product ID”, Copilot generates two XLOOKUP formulas or a single formula that chains them together. Copilot does not change the data in your sheets. It only inserts the formula into the cell you selected.
Prerequisites for Using Copilot with Multi-Sheet XLOOKUP
Before you start, verify these conditions:
- You have a Microsoft 365 subscription that includes Copilot for Microsoft 365. Copilot in Excel is not available in free or consumer versions.
- Your workbook contains at least two sheets with related data. Each sheet should have a common column, such as a product ID or employee number.
- The data on each sheet is formatted as an Excel table. Select your data range and press Ctrl+T to create a table. Give each table a descriptive name on the Table Design tab.
- Your Excel language is set to English. Copilot prompts work best in English for formula generation.
Steps to Generate a Multi-Sheet XLOOKUP Formula with Copilot
Follow these steps to have Copilot write the XLOOKUP formula that pulls data from two or more sheets.
- Open the Copilot pane in Excel
Click the Copilot icon on the Home tab of the ribbon. The Copilot pane opens on the right side of the Excel window. If you do not see the icon, contact your Microsoft 365 admin to verify your license. - Select the cell where you want the result
Click a single cell in your current sheet. This cell will contain the final XLOOKUP formula. Copilot inserts the formula into the active cell. - Type your natural language request
In the Copilot text box, type a prompt that describes the lookup. For example: “Look up the price from the Price table using the Product ID in column A of this sheet.” Be specific about which table and which column to search. If you need data from two tables, say: “Return the price from Table1 and the quantity from Table2, both matched by Product ID.” - Press Enter or click the Send button
Copilot processes your request and displays a suggested formula in the pane. The formula may appear as a single XLOOKUP or as multiple XLOOKUP calls nested inside IFERROR or combined with the ampersand operator. - Review the formula preview
Read the formula that Copilot shows. It will include sheet names and table references, such as=XLOOKUP([@[Product ID]],Price[Product ID],Price[Price]). Check that the lookup value column and return column are correct. - Click Insert or Apply
If the formula looks correct, click the Insert button in the Copilot pane. Copilot places the formula into the selected cell. If the formula is not what you wanted, click the thumbs-down icon and refine your prompt. - Test the formula with a few values
Enter a few lookup values in the source column and verify that the returned data matches the expected values from the other sheets. If the formula returns #N/A, check that the lookup value exists in the target sheet and that the data types match.
Refining the Prompt for Complex Lookups
If Copilot does not generate the correct formula on the first try, adjust your prompt. Use these patterns:
- “Create a formula that looks up the department name from the Departments table and the manager name from the Managers table, both using the Employee ID in column B.”
- “Combine XLOOKUP results from two sheets into one formula. First look up the price from the Price sheet, then look up the discount from the Discount sheet.”
- “Use IFERROR to return ‘Not found’ when the XLOOKUP from any sheet fails.”
Common Issues When Using Copilot with Multi-Sheet XLOOKUP
Even with a correct prompt, you may encounter problems. Here are the most frequent issues and how to resolve them.
Copilot Returns a Formula That References the Wrong Sheet
Copilot sometimes guesses the sheet name incorrectly. This happens when your tables have generic names like Table1 and Table2. Rename each table to something descriptive, such as SalesData or Inventory. Then rephrase your prompt to include the table name. For example: “Look up the cost from the Inventory table using the SKU in column A.”
The Formula Returns #N/A for Some Values
The #N/A error means the lookup value is not found in the target table. Check that the lookup column on the target sheet contains the exact same values as the source column. Watch for extra spaces, different number formats, or leading zeros. Use the TRIM function on both columns before running the lookup.
Copilot Does Not Suggest Any Formula
If Copilot responds with a general explanation instead of a formula, your data may not be in a table. Convert each range to a table by selecting it and pressing Ctrl+T. Then try the prompt again. Also confirm that the Copilot pane is connected to your Microsoft 365 tenant and that your admin has enabled the Copilot feature for Excel.
Copilot-Generated Formula vs Manual XLOOKUP: Key Differences
| Item | Copilot-Generated Formula | Manual XLOOKUP Formula |
|---|---|---|
| Speed of creation | Seconds after a natural language prompt | Minutes to hours depending on formula complexity |
| Accuracy on first attempt | High when tables are named and prompts are specific | High but requires testing and debugging |
| Handling of multiple sheets | Automatically references table names and sheet names | Requires manual sheet references like Sheet2!$A$1:$B$100 |
| Error handling | Often includes IFERROR or IFNA automatically | Must be added manually |
| Learning curve | Low — you only need to describe the task | High — you must know XLOOKUP syntax and sheet referencing |
You can now use Copilot in Excel to generate XLOOKUP formulas that pull data from multiple sheets without writing any syntax yourself. Start by formatting your data as tables and giving each table a clear name. Then describe the lookup in plain English in the Copilot pane. If the first formula is not perfect, refine your prompt with specific table and column names. For advanced scenarios, try combining Copilot with the VSTACK function to merge data from several sheets before running a single XLOOKUP.