Here is a step-by-step tutorial on how to use Microsoft Excel’s Co-pilot feature to perform lookups between worksheets.
Ensure that the data you want to use for the lookups is organised in separate worksheets. In the example from the video, there are three worksheets: “Transaction Sheet,” “Products Sheet,” and “Sales Rep Sheet.”
Step 2: Activate Co-pilot
Click on the Co-pilot button in Excel.
Step 3: Phrase Your Prompt
In the Co-pilot prompt, you need to:
- Specify the data you want to look up (e.g., “product description,” “product brand,” “sales rep’s name”).
- Indicate that the information is located on different worksheets.
- Name the corresponding worksheets for each lookup value.
For example, you could write: “Look up the product description and the product brand in the Products worksheet and the sales rep’s name in the Sales Rep worksheet.”
Step 4: Send the Prompt
Click on the send button to submit your prompt to Co-pilot.
Step 5: Review the Output
Co-pilot will generate the requested columns based on your prompt. You can verify that the output matches your expectations.
Step 6: Insert the Columns
Click on the “Insert columns” button to add the generated columns to your worksheet.
Step 7: Examine the Formulas
Co-pilot will use XLOOKUP formulas to perform the lookups. You can review these formulas to understand how Co-pilot has implemented the lookups. Note that the formulas might differ slightly from how you would write them manually.
By following these steps, you can leverage Co-pilot to perform lookups between worksheets in Excel without having to write the formulas yourself.