INDEX MATCH with Multiple Criteria

Perform Lookups Using Multiple Conditions

Standard `VLOOKUP` and `INDEX MATCH` are limited to searching for a single unique value. But what happens when you need to pull the sales figure for 'John Doe' (Criteria 1) in 'February' (Criteria 2)? Searching just for 'John Doe' will return his January data. Searching just for 'February' will return someone else's data. You need a multi-criteria lookup. This requires a complex Boolean array formula that multiplies conditions together. This generator writes that incredibly dense code for you.

How the Multi-Condition Array Works

The engine builds a Boolean logic array inside the `MATCH` function: `=INDEX(Return_Range, MATCH(1, (Criteria1=Range1)*(Criteria2=Range2), 0))`. In this formula, Excel evaluates each condition as TRUE (1) or FALSE (0). By multiplying them, only the row where BOTH conditions are TRUE results in a 1 (1x1=1). The MATCH function then searches for the '1', isolating the exact row that meets all your intersecting criteria, and returning the correct value from the INDEX.

Step-by-Step Usage

1. Enter the Return Range (the answer you want to pull, e.g., C:C).
2. Enter Search Value 1 (e.g., A2) and its corresponding Lookup Range (e.g., Sheet2!A:A).
3. Enter Search Value 2 (e.g., B2) and its corresponding Lookup Range (e.g., Sheet2!B:B).
4. Add more conditions if necessary.
5. Click the 'Generate Array Formula' button.
6. Review the generated Boolean array code.
7. Copy and paste into Excel.

Key Benefits

• Multi-Dimensional Search: Pinpoints exact data intersections in massive, repetitive databases.
• Zero Helper Columns: Bypasses the messy workaround of creating concatenated columns (e.g., combining First and Last names) just to run a standard VLOOKUP.
• Unbreakable Logic: The generated Boolean math is mathematically infallible.
• Saves Time: Array formulas are notoriously hard to memorize and type manually.

Real-World Use Cases

HR managers pull an employee's specific bonus amount by matching 'Employee ID' AND 'Performance Year'. Financial analysts extract pricing from a complex catalog by matching 'Product SKU' AND 'Region Tier'. Logistics planners retrieve shipping rates by matching 'Package Weight Bracket' AND 'Destination Zone'.

Pro Tips for the Best Results

Crucial Execution Step: In older versions of Excel (2019 and prior), the formula generated by this tool is considered a CSE (Control-Shift-Enter) Array Formula. After pasting the code into your formula bar, you cannot just hit 'Enter'. You MUST press Ctrl + Shift + Enter simultaneously. This wraps the formula in curly brackets `{}` and tells Excel to process the arrays. If you are using modern Excel 365, the dynamic array engine handles it automatically, and simply hitting 'Enter' works perfectly.

Top Use Cases

• Retrieving specific historical pricing based on Product ID and Date
• Extracting test scores by matching Student ID and Exam Subject
• Looking up localized shipping rates via Weight and Zone

Frequently Asked Questions