by With Excel Copilot, creating VLOOKUP and XLOOKUP formulas is as easy as explaining your task in plain language. This guide walks you through using Copilot in both the desktop Excel app and in browser to perform quick, accurate lookups – no advanced formula skills needed!
Sometimes you may hear comments from Excel pros "Argh, I never use it. It's faster to write a formula in a cell than explain to AI what you want that formula to do". But not everyone is a spreadsheet expert, especially when it comes to tricky functions like VLOOKUP. The ability to describe what you need in simple language is a great advantage for the average Excel user. Let's see how Copilot can help you create lookup formulas without breaking a sweat.
How to do VLOOKUP with Copilot in Excel
First things first. Let's outline the necessary preparatory steps to get started:
- Activate Copilot. If Copilot is available in your version of Excel, you'll see its icon on the Home tab of the ribbon. Click the icon to enable the AI assistant. If you don't see the icon, check out How to add Copilot to Excel.
- Auto-save workbook. Copilot works only with files stored on OneDrive or SharePoint and requires AutoSave to be turned on.
- Format as table. The AI assistant works with Excel tables, so if your data is in regular ranges, convert them into tables first. See Convert data range to table for detailed steps.
- Describe the task. In the Copilot chat box, type your request. Be as detailed as possible to help the AI generate the right formula.
- Apply the formula. Copilot will create the formula and show it in the chat box. You can either copy it to your sheet manually or click Insert column to let Copilot automatically apply the formula to your table.
- Ask for clarifications or edits. Need to understand how the formula works? Click Show explanation to see what each part does. If the result is not quite what you expected, simply ask Copilot to tweak the formula.
That's how Copilot can make a VLOOKUP formula more accessible with a simple prompt:
Now, let's put these steps into action with practical examples. For hands-on practice, feel free to download our sample workbook.
Example of XLOOKUP with Copilot
For this example, we'll work with two simple data sets shown in the screenshot below. The tables are named Main_table and Lookup_table to reflect their purpose. Both are placed on the same sheet for better visualization, but in your real workbooks, they can be on different tabs – Copilot can handle that too.
The task is to pull the Reseller names from the lookup table into the main table based on the Item column, which both tables have in common. For Copilot, you can describe it in very simple words:
Please bring over Reseller from Lookup_table into Main_table.
Pay attention to how the prompt refers to both tables by their actual names – this helps AI understand the task more accurately and generate the correct formula.
Copilot quickly produces the following XLOOKUP formula, which is a modern, more flexible alternative to VLOOKUP:
=XLOOKUP([@Item], Lookup_table[Item], Lookup_table[[Reseller]])
Clicking the Insert column button automatically adds the calculated Reseller column to the main table, placing it in column D.
Tip. If something doesn't work as expected, you can click the Undo button in the Copilot prompt. Just remember to do this before making any other changes - once you edit the sheet, the Undo option becomes unavailable.
Example of VLOOKUP with Copilot
In case you need to pull matches with a classic VLOOKUP formula, for example for compatibility with older versions of Excel that don't support XLOOKUP, you can ask Copilot directly to use that function. For example, your request might be:
Pull Resellers from Lookup_table into Main_table using a VLOOKUP formula.
If Copilot has already created an XLOOKUP formula for you, simply ask to switch to VLOOKUP. A prompt like this will do the trick:
Could you use the VLOOKUP function instead of VLOOKUP.
In a moment, Copilot will generate the following formula:
=VLOOKUP([@Item], Lookup_table, 2, FALSE)
To apply the formula, click the Copy icon in the Copilot response to copy the formula to your clipboard. Then paste it into the first data cell of the target column (D in our case). Since the data is formatted as an Excel table, the entire column will automatically fill with the new formula.
Excel Copilot to VLOOKUP / XLOOKUP from another sheet
In many real-life workbooks, the datasets are often stored on different tabs. Let's see how Copilot handles this situation.
Suppose you have two tables placed on different worksheets withing the same workbook, which are named respectively: Main table and Lookup table. Both tables share a common column Reseller, and your goal is to pull company names from the lookup table into the main table based on matching reseller names.
To get the job done, you can start with a simple prompt like this:
Pull company names from the Lookup table sheet to the Main table.
Copilot understands the request perfectly and produces this formula:
=XLOOKUP([@Reseller], Table6[[Reseller]], Table6[Company])
Notice that the formula doesn't include the sheet name. That's because table names in Excel have Workbook scope by default, meaning they can be referenced from any worksheet without needing the sheet name. Interestingly, in this example, the tables have their default names (like Table6), yet Copilot detects them without any problem.
Clicking the Insert column button places the company names into column E automatically:
If you'd prefer a VLOOKUP formula instead of XLOOKUP, just ask! However, note that Copilot cannot replace formulas already applied to the table:
To get a VLOOKUP formula, you can request something like this:
Write a VLOOKUP formula that will pull the company names from the Lookup table tab.
And Copilot immediately comes up with this one:
=VLOOKUP([@Reseller], 'Lookup table'!A:B, 2, FALSE)
For some reason, Copilot uses a mixed reference this time, referring to the lookup column by its name [@Reseller], but making an external reference for the lookup range ('Lookup table'!A:B).
If you like order and consistency in your sheets, like I do, you can ask Copilot to refine the formula:
In this formula, can you refer to the lookup table by its name?
Surprisingly, AI struggles with this simple request, which is quite odd, considering it handled a similar XLOOKUP without a hitch. But I'm persistent, you know 😉 After a short back-and-forth, I finally get the result I want. Sure, I could have fixed the reference myself much faster, but at this point, it's a matter of principle!
For more information, please see How to VLOOKUP across multiple sheets in Excel
Using web Copilot to VLOOKUP across different Excel files
In the previous examples, we used Copilot integrated directly into Excel. But what if you don't have it in your desktop Excel app yet? No worries, the free web version of Microsoft Copilot can help with your lookup tasks too. The only difference is that the web version can't access your Excel sheets, so you'll need to be more descriptive when explaining your request.
On the bright side, you don't need to format your data as a table – web Copilot can generate formulas with regular range references.
To get an accurate formula, be sure to describe the structure of your data, including these key details:
- The exact file and sheet names.
- Which columns contain the lookup values (unique identifiers) and which columns contain the return data (the values you want to bring over).
- The starting row of your data – Excel formulas are usually written for the top cell, so this helps Copilot reference the right cells.
- The last row of your data, so Copilot can use the correct reference to the cell where your range ends.
For this example, let's try something that Excel's built-in Copilot can't do – VLOOKUP between two different workbooks. Imagine you have two Excel files like shown in the screenshot below. The goal is to pull prices from column B of the Prices.xlsx into the Orders.xlsx file based on the item names.
To explain the task to Copilot, I type the following prompt:
I have 2 Excel files, named Orders and Prices. The Orders file has the sheet named Jan with 3 columns of data - column A (order id), column B (item) and column C (qty.). The Prices file has Sheet1 with 2 columns of data - column A (item) and column B (price). In both files, the data starts in row 3. I need a formula to bring over the prices from the Prices file to the Orders file.
And just look at that! Copilot has crafted a ready-to-use VLOOKUP formula along with detailed instructions on where to insert it and what adjustments might be needed:
=VLOOKUP(B3, '[Prices.xlsx]Sheet1'!$A$3:$B$100, 2, FALSE)
The formula works perfectly! The only tweak needed is the lookup range – AI assumed the data goes down to row 100 because I didn't specify where the price list ends. That's on me 😉
If your data ends at row 22, you can correct the reference to $A$3:$B$22. Or leave a larger to allow for future data additions.
For the sake of curiosity, I asked Copilot to create an analogous XLOOKUP formula – this time, specifying the correct range. And voilà! The result is absolutely perfect:
=XLOOKUP(B3, '[Prices.xlsx]Sheet1'!$A$3:$A$22, '[Prices.xlsx]Sheet1'!$B$3:$B$22, "Not found")
Copilot even added the optional if_not_found argument to return "Not found" instead of the #N/A error if no match is found, just like in the last row of the screenshot below:
Not bad for a virtual assistant, huh? 😊
Limitations of VLOOKUP with Excel Copilot
For performing various lookup tasks in Excel, Copilot can be a helpful assistant – but it does have certain limitations:
- Cross-workbook lookups. Excel's built-in Copilot cannot generate formulas to look up between different workbooks. If you need to pull data from another file, try using Copilot in browser, where you can describe your task in detail and get a working formula.
- Multiple matches and criteria. Copilot cannot generate lookup formulas that return multiple results or handle multiple conditions. For such cases, it suggests using the FILTER function, which is available in Excel 365 and Excel 2021. For earlier Excel versions, you can follow our guide on how to VLOOKUP multiple values with criteria.
- Works with tables only. Copilot relies on Excel tables to generate formulas. If you can't convert your data to a table for some reason, use Copilot on the web instead. It can generate formulas with regular range references, which you can manually copy into your sheet.
- Large datasets may cause errors. While Copilot is theoretically designed to handle tables with up to two million cells, its performance can be unpredictable. In tests with a table of 500,000 rows and three columns, Copilot repeatedly returned the error: Something went wrong. Restart Excel and try sending the prompt again.
Tip. For processing large datasets quickly and reliably, you can use the Merge Tables Wizard included with our Ultimate Suite – a tool capable of looking up and merging thousands of rows in just a few seconds and clicks. For a detailed comparison of different lookup methods, check out our guide: VLOOKUP in Excel - which formula is fastest?
Tips for effective lookups with Excel Copilot
To improve the accuracy of AI suggestions and speed up your work with lookup formulas in Excel, follow these simple tips:
- Name your tables meaningfully. Instead of using the default names like Table1 or Table2, assign descriptive names that reflect the table's purpose. This makes your prompts more intuitive and helps Copilot craft more accurate formulas, especially for complex data sets.
- Be specific with prompts. Clearly describe your request, including table names, column names, and what result you're aiming to get. The more context you provide, the better Copilot will understand your task. If you're not satisfied with the result, rephrase the prompt with more details.
- Specify the desired function. If you prefer VLOOKUP over XLOOKUP (for example, for compatibility with older versions), mention it explicitly in your prompt. Otherwise, Copilot will default to XLOOKUP, which is the more modern function.
- Learn Excel tables and references. In most cases, Copilot's formulas rely on structured references. Familiarize yourself with how Excel tables work to better understand and troubleshoot the formulas Copilot generates.
- Use Copilot on the web for ranges. If your data is not formatted as a table, try Copilot in browser. The web version can build formulas with regular range references, which is helpful when tables aren't an option.
- Test with small data sets. If Excel Copilot struggles with large datasets, test the formula on a smaller data portion first. Once you confirm it's working correctly, apply it to the full dataset manually.
- Don't give up easily. If Copilot's first suggestion isn't what you need, try rephrasing your request. Sometimes, a slight adjustment in how you describe the task can lead to better results.
Wrapping up: Although AI isn't flawless, it provides a convenient way to generate VLOOKUP and XLOOKUP formulas, especially for those who find it easier to describe tasks in natural language rather than craft formulas from scratch. While Copilot may not always get it right on the first try, a little persistence and clear instructions can make all the difference. Give it a try, experiment with different prompts, and see how Copilot can lighten your Excel workload 😊