Return a value only when two or more conditions match. Build an auditable INDEX and MATCH formula, use safe helper keys when useful, and detect duplicate criteria before trusting the first result.
I have this Excel table in A2:C5: Region | Product | Price North | Widget | 10 South | Widget | 12 North | Gadget | 15 South | Gadget | 18 Cell E2 contains South and F2 contains Gadget. Give me an INDEX + MATCH formula that returns Price only where both Region and Product match. Use exact matching and do not use XLOOKUP as the main solution. Provide: 1. the dynamic-array/current Excel formula; 2. the same INDEX/MATCH multiple-criteria formula with a note about Ctrl+Shift+Enter in older Excel versions where required; 3. the exact expected result; 4. an explanation of what each Boolean condition contributes. Also explain why matching only Product would return an ambiguous or incorrect row.
Test 1 of 3 — approved PASS and preserved as tested.
I need a three-criteria lookup in Excel. Data in A2:D4: Region | Product | Month | Rate North | A-10 | Jan | 5.5 North-A | 10 | Jan | 7.0 North | A-10 | Feb | 6.0 Criteria: F2 = North G2 = A-10 H2 = Jan Show two INDEX/MATCH solutions: 1. a direct Boolean-array formula using all three criteria; 2. a helper-key method suitable for easier auditing. For the helper key, use a delimiter that cannot be confused with the hyphens already present in the source values, and explain why concatenating values with no delimiter can create collisions. Preserve the original columns. State the exact expected result. Include exact-match handling and do not use XLOOKUP as the main answer.
Test 2 of 3 — approved PASS and preserved as tested.
I have this Excel data in A2:D5: Customer | Product | Date | Amount Acme | Widget | 2026-01-05 | 100 Acme | Widget | 2026-01-12 | 125 Acme | Gadget | 2026-01-20 | 80 Beta | Widget | 2026-01-07 | 90 Criteria: F2 = Acme G2 = Widget I want to use INDEX + MATCH with multiple criteria, but this combination has two matching rows. Explain exactly what the standard INDEX/MATCH multiple-criteria formula returns and why. Then provide: 1. the standard formula; 2. a COUNTIFS check that warns when the criteria are not unique; 3. a safe formula pattern that returns "Multiple matches" instead of silently treating the first row as unique; 4. an optional modern alternative for returning all matches, mentioned only as a note. Do not invent a rule such as latest date or highest amount unless I explicitly choose it. State the expected match count and the expected result of the standard formula.
Test 3 of 3 — approved PASS and preserved as tested.
| Source condition | Risk |
|---|---|
| South + Gadget | Matching Product alone can return North's price 15. |
| Output | Expected result |
|---|---|
| Exact two-criteria match | INDEX/MATCH returns 18 |
| Source condition | Risk |
|---|---|
| North + A-10 + Jan | Concatenation without a delimiter can create ambiguous helper keys. |
| Output | Expected result |
|---|---|
| Three criteria | Direct array or delimited helper key returns 5.5 |
| Source condition | Risk |
|---|---|
| Acme + Widget | Two rows satisfy the criteria; a standard lookup hides the ambiguity. |
| Output | Expected result |
|---|---|
| Duplicate warning | COUNTIFS = 2; standard formula returns first Amount 100 |
List the exact return range and each criterion range; all ranges must cover the same rows.
Multiply equality tests so only rows satisfying every condition evaluate to 1.
Use MATCH(1,array,0) inside INDEX. In older Excel versions, confirm whether Ctrl+Shift+Enter is required.
When using a helper column, preserve source fields and separate components with a delimiter that cannot collide with real values.
Use COUNTIFS before trusting the result. Return Multiple matches or Not found when the business key is not exactly one row.