🧪 Exercise 01: Generate a Multi-Condition Lookup Formula With AI

Objective

Learn how to construct a constrained prompt to generate a modern multi-condition lookup formula in Excel, inspect the AI's output, and verify the resulting syntax.

Dataset

Table_Sales (Columns: OrderDate, Region, SalesRep, ProductCategory, Revenue):

• Row 1: 2024-01-15, North, Sarah, Hardware, $4,500
• Row 2: 2024-01-16, South, James, Software, $1,200
• Row 3: 2024-01-17, North, Sarah, Services, $3,100

Business Scenario

Management needs to dynamically retrieve the revenue generated by Sarah strictly for the Hardware category. The formula must not use volatile functions or slow nested loops.

Task

1. Draft a structured prompt using the template from AI Excel Prompt Library.
2. Submit the prompt to your AI assistant (Twistly or Claude).
3. Evaluate the generated formula against Excel 2021/M365 compatibility.

AI Prompt

Task: Retrieve Revenue for a specific SalesRep and ProductCategory.
Workbook context:
- Table: Table_Sales
- Lookup Columns: Table_Sales[SalesRep], Table_Sales[ProductCategory]
- Return Column: Table_Sales[Revenue]
- Inputs: Cell H2 = "Sarah", Cell H3 = "Hardware"
Constraints:
- Use modern XLOOKUP with boolean array multiplication.
- Do not use legacy array CSE (Ctrl+Shift+Enter) or volatile OFFSET.
- Return $0 if no matching transaction is found.
Explain the logic step-by-step.

Expected Reasoning

The AI should recognize that Excel's modern XLOOKUP supports multi-condition lookups using boolean logic: 1 = (Table_Sales[SalesRep] = H2) * (Table_Sales[ProductCategory] = H3) Multiplying boolean arrays converts TRUE/FALSE into 1/0. The first instance of 1 identifies the target row index.

Validation

Manual Solution

=XLOOKUP(1, (Table_Sales[SalesRep]=H2) * (Table_Sales[ProductCategory]=H3), Table_Sales[Revenue], 0)

=IFERROR(INDEX(FILTER(Table_Sales[Revenue], (Table_Sales[SalesRep]=H2) * (Table_Sales[ProductCategory]=H3)), 1), 0)

Reflection

Did the AI attempt to generate a legacy INDEX/MATCH or suggest SUMIFS? Note that SUMIFS adds numbers together, whereas XLOOKUP retrieves the specific single transaction record. Understanding the difference between aggregation and record retrieval is a critical human insight.

Lessons Learned

• AI provides fast syntax for boolean array multiplication in XLOOKUP.
• Explicitly specifying the "if not found" parameter (0) protects dashboards against ugly #N/A errors.