Do you find VLOOKUP and XLOOKUP confusing? What if you could perform complex lookups without writing a single formula? Thanks to Microsoft Copilot in Excel, you can now use plain English to automate data matching—saving time and reducing errors.
In this article, we’ll walk through how AI can simplify Excel lookups, using a real-world example with a cookie sales dataset.
Why Use AI Instead of VLOOKUP?
Traditional Excel lookups require:
❌ Memorizing complex syntax (e.g., =VLOOKUP(value, table, column, FALSE)).
❌ Manually defining ranges and columns.
❌ Debugging errors like #N/A.
With Copilot in Excel, you can:
✔ Describe what you need in plain English (e.g., “Fill in all the cookie prices”).
✔ Let AI generate the correct formula automatically.
✔ Get dynamic updates when source data changes.
Step-by-Step: AI-Powered Lookups in Excel
1. Format Your Data as Tables
For Copilot to work effectively, your data should be in structured Excel tables:
- Select your data (e.g., Cookie Orders).
- Press Ctrl + T (or go to Insert > Table).
- Confirm “My table has headers” and click OK.
- Name your table (e.g., “Cookie Orders”):
- Go to Table Design > Table Name.
Repeat for the second dataset (e.g., Cookie Prices).
2. Enable Copilot (Requires Microsoft 365 & Cloud Saving)
- Click the Copilot button (on the Home tab or as a floating icon).
- If prompted, enable AutoSave and store the file in OneDrive/SharePoint.
3. Ask Copilot to Perform the Lookup
In the Copilot pane, type a simple command like:
“Fill in all the cookie prices from the ‘Cookie Prices’ table.”
Copilot will:
✅ Generate the correct XLOOKUP formula.
✅ Show a preview before inserting data.
✅ Explain the formula if needed (click “Show Explanation”).
4. Insert the Results
- Hover over “Insert Column” to preview.
- Click to apply the changes.
Now, your data is automatically linked—if prices update in the Cookie Prices table, they’ll instantly reflect in the Cookie Orders table!
Why XLOOKUP is Better Than VLOOKUP
Copilot prefers XLOOKUP because it:
✔ Works in any direction (left/right lookups).
✔ Handles errors better (no more #N/A issues).
✔ Is more flexible (simpler syntax).
Example of the AI-generated formula:
=XLOOKUP([@Cookie], 'Cookie Prices'[Cookie], 'Cookie Prices'[Price])Bonus: Automate More with Copilot
Beyond lookups, Copilot can:
✔ Summarize data (e.g., “Show total sales per cookie”).
✔ Create charts (e.g., “Plot a bar graph of orders”).
✔ Clean data (e.g., “Remove duplicate cookie entries”).
Final Thoughts
Gone are the days of struggling with VLOOKUP syntax. With AI in Excel:
- No formulas to memorize—just describe what you need.
- Dynamic updates keep your data accurate.
- Faster workflows mean more time for analysis (or eating cookies!).
Try it yourself! Open Excel, format your data as tables, and let Copilot handle the rest.
Tags:
Excel, Microsoft 365, Copilot, VLOOKUP, XLOOKUP, AI, data analysis, Excel tips, spreadsheet automation
Hashtags: #ExcelTips #Microsoft365 #AI #DataAnalysis #ExcelAutomation #Copilot #XLOOKUP #VLOOKUP #SpreadsheetHacks
