Using INDEX MATCH multiple criteria lets Excel return a value only when several conditions match the same row. The method works well when one field cannot identify the correct record. You can use it for sales, inventory, employee, pricing, and reporting data.
Direct answer: Use \=INDEX(return_range,MATCH(1,(criteria1=range1)*(criteria2=range2),0)) to match several conditions in Excel. Each comparison returns TRUE or FALSE. Multiplication converts matching rows into 1 values, and MATCH finds the first row where every required condition equals TRUE.
| Key point | What you need to know |
|---|---|
| Main formula | \=INDEX(return_range,MATCH(1,(criteria1=range1)*(criteria2=range2),0)) |
| Two criteria | Add two Boolean comparisons inside MATCH |
| Three criteria | Multiply a third comparison into the lookup array |
| Match type | Use 0 for an exact match |
| Microsoft 365 | Usually confirm with Enter |
| Older Excel | Some array formulas require Ctrl+Shift+Enter |
| Duplicate matches | The formula normally returns the first matching row |
| Modern alternatives | XLOOKUP and FILTER can simplify some tasks |
How INDEX MATCH Multiple Criteria Works in Excel
A standard lookup often checks one field, such as an employee ID or product code. Real worksheets frequently contain repeated values within those fields. Adding another condition helps Excel identify the correct row.
The technique creates a temporary array of TRUE and FALSE results. Excel treats TRUE as 1 and FALSE as 0 during multiplication. Only a row satisfying every condition produces a final value of 1.
MATCH searches that temporary array for the first 1. It returns that row position to INDEX. INDEX then retrieves the corresponding value from your chosen return range.
Exceljet documents the same Boolean multiplication pattern for several criteria. Its example combines size, color, and product criteria before returning a price. This approach remains useful when you need flexible exact-match lookups.
Basic Formula for Two Criteria
Suppose a worksheet stores products in column A and regions in column B. Column C contains sales amounts. You want the sales amount for Product B in the West region.
Use this formula:
\=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))
Cell F2 contains the required product. Cell G2 contains the required region. Excel checks both conditions against every applicable row.
The multiplication sign represents AND logic in this formula. Both comparisons must be TRUE before their product becomes 1. MATCH then locates the first qualifying record.
This structure is useful when customer names, products, departments, or locations appear repeatedly. A second field makes the lookup more specific. More conditions can be added using the same pattern.
Step-by-Step: Build the Formula Correctly
1. Choose the return range. Select the cells containing the value you want returned. 2. Define the first condition. Compare your first lookup cell with its corresponding data range. 3. Add the second condition. Multiply the next comparison by the first comparison. 4. Set MATCH to find 1. Use MATCH(1,combined_conditions,0) for an exact match. 5. Place MATCH inside INDEX. Let the returned row number select the requested value. 6. Confirm the formula. Press Enter in current Microsoft 365 versions. 7. Test several records. Check known results before applying the formula throughout your workbook.
Microsoft explains that current Microsoft 365 supports dynamic-array processing with the Enter key. Older Excel versions can require Ctrl+Shift+Enter for legacy array formulas. Never type curly braces around a legacy formula yourself.
For more spreadsheet reporting techniques, see TechnoLoss’s pivot table guide. Pivot tables solve different problems, but they pair well with lookup formulas. One retrieves records while the other summarizes larger datasets.
INDEX MATCH With Three or More Conditions
The same method works when two criteria are not enough. Add another comparison and multiply it with the existing conditions. Every required field must match on the same row.
For three conditions, use:
\=INDEX(D2:D100,MATCH(1,(A2:A100=F2)(B2:B100=G2)(C2:C100=H2),0))
Imagine column A contains a product, column B contains a state, and column C contains a quarter. Column D contains revenue. The formula can return revenue for one product, state, and quarter combination.
You can extend this method further when your data requires another identifying field. Keep every criteria range aligned to the same rows. Misaligned ranges can produce errors or incorrect results.
A Practical U.S. Sales Example
Consider a U.S. retailer tracking orders by product, state, and sales channel. The same product can appear hundreds of times. Looking up the product alone would return an unreliable result.
Suppose F2 contains “Laptop,” G2 contains “Texas,” and H2 contains “Online.” The lookup can require all three values simultaneously. INDEX then returns the corresponding revenue or order value.
This structure works especially well for fixed report cells. A manager can change the three criteria cells without editing the formula. The returned value updates according to those selections.
If you need broader summaries instead, a pivot table may be easier. Use formulas when one cell needs a precise result. Use summaries when you need grouped totals across many records.
Why MATCH Looks for the Number 1
The number 1 is not an arbitrary lookup value. It represents a row where every required comparison evaluates as TRUE. Multiplication makes this possible without creating a helper column.
Consider two conditions for one row. TRUE multiplied by TRUE produces 1. TRUE multiplied by FALSE produces 0.
Rows failing either condition therefore become zero. MATCH searches for an exact 1 using its final 0 argument. The first 1 identifies the first row satisfying every condition.
This Boolean logic is the central idea behind the formula. Understanding it makes troubleshooting much easier. It also helps when adding a third or fourth condition.
Microsoft 365 vs. Older Excel Versions
Current Microsoft 365 versions support dynamic-array behavior for many formulas. Microsoft says users can often enter array formulas and press Enter. Legacy Excel uses Ctrl+Shift+Enter for many array-based calculations.
You may therefore see older tutorials showing formulas inside curly braces. Those braces indicate a legacy array formula. Excel adds them automatically after Ctrl+Shift+Enter.
Microsoft also notes that legacy array formulas remain supported for compatibility. Dynamic arrays reduce the need for older entry methods. Your workbook’s Excel version determines which workflow applies.
For other Microsoft application shortcuts, TechnoLoss also covers check mark methods in Excel and Word. The guide explains where Windows and Office shortcuts behave differently. That distinction matters when instructions vary between software versions.
Common Errors and How to Fix Them
| Problem | Likely cause | Fix |
|---|---|---|
| #N/A | No row meets every condition | Check spelling, spaces, and lookup values |
| #VALUE! | Array handling or range problem | Check Excel version and formula entry |
| Wrong result | Duplicate qualifying rows | Add another criterion or unique identifier |
| Formula returns first duplicate | MATCH finds the first 1 | Use FILTER when you need every match |
| Unexpected mismatch | Numbers stored as text | Standardize data types |
| Error after editing ranges | Ranges have different sizes | Make every criteria range align |
A #N/A result usually means Excel found no exact combination. Check hidden spaces and inconsistent capitalization in your source data. Also confirm that numbers are not stored as text.
A #VALUE! error can appear when an older Excel version expects array entry. Microsoft specifically documents array-entry issues with INDEX and MATCH formulas. Verify your version before rewriting a formula that appears structurally correct.
Incorrect results can also come from duplicates. MATCH returns the first qualifying position it finds. Add another identifying condition when the dataset contains several valid records.
INDEX MATCH vs. XLOOKUP for Multiple Conditions
Modern Excel users can also build multiple-condition lookups with XLOOKUP. For example, Boolean comparisons can be multiplied inside its lookup array. This may produce a shorter formula for newer workbooks.
INDEX with MATCH remains useful for older files and established reporting models. It is also familiar to teams maintaining workbooks created before XLOOKUP became common. The right choice depends on compatibility and workbook requirements.
Microsoft notes that XLOOKUP can simplify many modern lookup scenarios. That does not make older lookup methods useless. Existing workbooks may still depend on them for compatibility or established logic.
TechnoLoss publishes more practical application coverage in its software section. You can also browse the site’s how-to guides for additional troubleshooting tutorials. Both sections provide related software and productivity guidance.
When FILTER Is a Better Choice
The formula above normally returns the first row meeting every requirement. That behavior is useful when your combination should identify one record. It is less useful when several records are valid.
Microsoft 365 users can use FILTER when they need all qualifying rows. FILTER can return several matching results as a dynamic array. This makes it useful for transaction lists and repeated records.
Choose the method based on the desired output. Use a single-value lookup when one result is expected. Use a filtering function when every matching record matters.
Tips for More Reliable Multi-Criteria Lookups
Keep all lookup ranges the same size and aligned to identical row numbers. A return range starting at row two should match criteria ranges starting there. Consistent ranges make formulas easier to audit.
Avoid full-column array references in large workbooks when performance becomes slow. Bounded ranges reduce unnecessary calculations. Excel Tables can also make growing datasets easier to maintain.
Clean source data before blaming the formula. Extra spaces, text-formatted numbers, and inconsistent labels cause many lookup failures. Testing against a known record can reveal those issues quickly.
Document complex formulas when coworkers will maintain the workbook. A short note explaining each criteria cell can prevent later mistakes. Clear workbook design matters as much as formula syntax.
Frequently Asked Questions
Does INDEX MATCH multiple criteria work with two conditions?
Yes. Multiply two Boolean comparisons inside MATCH and search the result for 1. INDEX then returns the value from the first row satisfying both conditions.
Keep both criteria ranges aligned with the return range. Use exact-match mode with the final MATCH argument set to zero. Current Microsoft 365 users can normally confirm the formula with Enter.
Can I add three or four criteria?
Yes. Add another parenthesized comparison for each required field. Multiply each comparison with the others to create AND logic.
Every condition must evaluate as TRUE on the same row. Otherwise, that row produces zero instead of one. MATCH therefore ignores rows failing any required condition.
Why does the formula return only the first matching result?
MATCH searches until it finds its first exact 1. It then returns that position and stops searching. INDEX consequently retrieves one corresponding value.
Use another distinguishing criterion when only one record should qualify. Use FILTER in supported Excel versions when you need several matching rows. That function can spill multiple results into neighboring cells.
Do I need Ctrl+Shift+Enter?
Current Microsoft 365 versions usually let you press Enter for dynamic-array calculations. Microsoft confirms that older versions can require Ctrl+Shift+Enter for legacy arrays. Your Excel version determines the required entry method.
Do not manually type curly braces around the formula. Legacy Excel adds those braces after successful array entry. Microsoft 365 generally does not require that legacy step.
Is XLOOKUP better than this method?
XLOOKUP is often simpler in newer Excel versions. It can handle many common lookup situations with less formula nesting. Compatibility can still make the older pattern useful.
Use the method your workbook environment supports reliably. Shared corporate files may need to work across older installations. New Microsoft 365 files can often use newer functions instead.
Choose the Lookup Method That Matches Your Data
Multi-condition lookups are most useful when one field cannot uniquely identify a record. Combining Boolean conditions lets Excel locate a row using several pieces of information. The technique remains practical for reporting, pricing, inventory, and operational workbooks.
Start with two criteria and test a record whose answer you already know. Add more conditions only when your data requires them. This approach keeps formulas readable and easier to troubleshoot.
For larger reporting tasks, combine precise lookups with summaries such as TechnoLoss’s pivot table workflow. That gives you both cell-level retrieval and broader analysis options. Explore more TechnoLoss software guides as your workbook becomes more advanced.
