To return a value in Excel by matching more than one condition, combine the tests inside MATCH and use the result as the row number for INDEX. For example, with departments in A2:A100, regions in B2:B100, return values in C2:C100, and the requested department and region in G2 and H2, use =INDEX(C2:C100,MATCH(1,(A2:A100=G2)*(B2:B100=H2),0)).
What INDEX MATCH with multiple criteria does
INDEX returns a value from a range at a position you specify, while MATCH finds the position of a requested value in a range. Used together, they can look up a result using several conditions instead of just one. In the example above, Excel searches for the row where the department in column A equals G2 and the region in column B equals H2, then returns the corresponding entry from column C.
The multiplication sign between the tests makes the conditions work as an AND: both must be true for a row to qualify. This is useful when a value such as a product, employee, or price is not uniquely identified by one field alone. A product name might appear in several regions, for example, so the product and region together identify the intended record.
The method does not require a special multiple-criteria version of INDEX or MATCH. It builds an array of results from the criteria tests, then asks MATCH to find the first position containing 1. The final argument of MATCH is 0, which requests an exact match.
Set up the data and lookup cells
For a clear example, suppose a worksheet has a department in column A, a region in column B, and a representative’s name in column C. Records begin on row 2 and continue through row 100. Put the department to search for in G2 and the region in H2. The ranges used in the formula must cover the same rows; otherwise, the tests and return values may refer to different records.
If G2 contains Hardware and H2 contains West, Excel evaluates the department and region tests row by row. If row 2 is Hardware / West / Maya, row 3 is Hardware / East / Omar, and row 4 is Hardware / West / Lena, both row 2 and row 4 meet the criteria. The formula returns Maya because it finds the first qualifying row. This first-match behavior matters when duplicate combinations exist.
Keep lookup criteria and source data consistent. A criterion typed in G2 must match the text in column A, including any extra spaces or spelling differences. For a more maintainable workbook, you can use Excel Tables and structured references, but the range-based version is often easier to understand when learning the method.
How the formula evaluates each criterion
With departments in A2:A100 and the requested department in G2, the test A2:A100=G2 compares each cell in that range with G2. It produces an array of TRUE and FALSE values. With regions in B2:B100 and the requested region in H2, B2:B100=H2 produces another array. Multiplying the two arrays converts TRUE to 1 and FALSE to 0, so only a row that passes both tests produces 1.
Then MATCH searches that combined array for 1. Its third argument, 0, means exact match. If the first qualifying record is the second row of the lookup range, MATCH returns 1, because the range starts at row 2; INDEX uses that relative position to return the corresponding item from C2:C100. It does not return the worksheet row number itself.
In Excel for Microsoft 365 on Windows, enter the formula normally and press Enter. Current Microsoft 365 Excel calculates this array operation without requiring a special array-entry shortcut. In older Excel versions that do not support dynamic arrays, this construction may need to be confirmed as an array formula with Ctrl+Shift+Enter on Windows. On Excel for Mac, the equivalent legacy array-entry shortcut is Command+Shift+Enter. Excel for the web supports modern Excel formulas and ordinary Enter entry.
Make the formula easier to maintain
Use bounded ranges such as row 2 through row 100, rather than entire columns, when you want to make the calculation’s scope obvious and limit unnecessary work in a large workbook. If records may be added, extend the ranges or convert the dataset to an Excel Table so the data range can expand as the table grows. Ensure all criteria ranges and the return range cover the same records.
If a missing match should display a friendly message instead of an error, wrap the lookup in IFERROR. With the same department, region, and return ranges as above, and G2 and H2 holding the lookup criteria, use =IFERROR(INDEX(C2:C100,MATCH(1,(A2:A100=G2)*(B2:B100=H2),0)),"Not found"). It returns the matching value when one exists and Not found if the lookup produces an error. This can make a report easier to read, but it can also hide other errors, so use it when that trade-off is appropriate.
For ongoing workbooks, consider naming important ranges or documenting what each lookup cell expects. For example, a note beside G2 and H2 can state that they require an exact department and region from the source data. Clear labels reduce the chance that someone enters a partial name or swaps the two criteria.
When records can have duplicate matches
INDEX and MATCH in this form return the first row that meets all conditions. They do not warn you that another row has the same combination. In the sample data, Hardware and West occur twice, so the answer is Maya even though Lena also meets the conditions. That is correct for a first-match lookup, but not necessarily correct for a task that expects a unique record.
Before relying on the result, decide what duplicates mean in the data. They may be legitimate records, such as multiple sales on the same day, or they may indicate an issue that should be corrected. You can add another condition, such as a date or order number, if it distinguishes the intended record. If the goal is to list every matching row rather than return just one, a filtering approach is more suitable than this single-result formula.
Also distinguish between a duplicate combination and a duplicate return value. Two different records may both return the same name or status, so seeing a plausible result does not prove the lookup combination is unique. A unique identifier or a separate duplicate check can provide more confidence when accuracy is important.
Alternative: use a helper column
A helper column can make the lookup logic more visible, especially for colleagues who find array calculations difficult to inspect. In D2, with the department in A2 and region in B2, enter =A2&"|"&B2 and fill it down through the records. This creates a combined key such as Hardware|West. The separator helps distinguish the two fields, though no separator is completely safe if it can also appear in the original values.
With those combined keys in D2:D100, the names in C2:C100, and the requested department and region in G2 and H2, use =INDEX(C2:C100,MATCH(G2&"|"&H2,D2:D100,0)). This returns the name associated with the first matching combined key. Because the helper column contains ordinary values built from each row, the calculation can be easier to audit than multiplying two arrays inside MATCH.
The helper-column approach has trade-offs. It adds a column to the dataset, and concatenated values can become ambiguous if the chosen separator occurs in the data. For example, fields containing the separator can accidentally create the same combined text from different inputs. If you use this approach, choose a separator that is not present in the data or use a more robust key design, and keep the helper formula filled down for new records.
Common errors and ways to troubleshoot
- #N/A: No row may satisfy every criterion, or a value may differ because of a typo, extra space, or inconsistent data type. Check each condition against the source data and verify that the lookup cells contain the intended values.
- Unexpected first result: More than one record may meet the conditions. Remember that this formula returns the first match in the search range, not an arbitrary or newest record.
- Wrong return value: Check that the return range and both criteria ranges start and end on the same rows. A misaligned range can make Excel return a value from a different record.
- Numbers stored as text: A numeric value stored as text may not compare the way you expect against a real number. Check the source and lookup cells, and standardize the data rather than masking the mismatch in a complicated formula.
- Dates that do not match: Excel stores dates as serial numbers, but cells can also include times. Two cells that display the same date may have different underlying values if one contains a time component.
- Spaces in text: Leading or trailing spaces can prevent exact text comparisons. Inspect the source values and clean them consistently if needed; do not assume that visually similar text is identical.
When debugging, temporarily inspect each condition separately in unused cells or narrow the ranges to a few known records. That helps determine whether the issue lies in the criteria, the data, or the alignment of the ranges. Avoid changing several parts of the formula at once, because it becomes harder to identify the cause.
INDEX MATCH compared with other lookup choices
For a two-condition exact lookup, INDEX MATCH is useful because it works in older Excel versions and lets you choose a return range independently from the search ranges. This can be convenient if the return column is to the left of a lookup column, or if the workbook must remain compatible with versions that lack newer lookup functions.
XLOOKUP is another option in Microsoft 365 and Excel 2021 or later. It can perform a lookup and return a result without nesting INDEX and MATCH, but multiple criteria still need to be expressed in its lookup logic. Excel versions before Excel 2021 do not include XLOOKUP, so INDEX MATCH remains relevant for compatibility. Choose based on the workbook’s supported versions, the complexity of the lookup, and what other users can readily maintain.
If you need all matching rows, rather than only the first, Excel 2021 and Microsoft 365 include FILTER, which can return multiple results based on criteria. If the task is to calculate a total across all qualifying rows, a conditional aggregation function such as SUMIFS is more appropriate than returning one record with INDEX MATCH. The right method depends on whether the desired output is one value, a list, or an aggregate.
Frequently Asked Questions
Can INDEX MATCH use three or more criteria?
Yes. Add another comparison inside the MATCH lookup array and multiply it by the other comparisons. Each test must use a range aligned to the same rows, and every criterion must be true for a row to qualify.
Does the formula return the last matching row?
No. The standard construction shown here returns the first match in the range. To return the last match, use a different lookup design or reverse the search order with an approach supported by your Excel version.
Why does MATCH use 1 and 0?
The multiplication of the TRUE/FALSE criteria tests creates 1 only where all conditions are true. MATCH searches for that 1, and its final argument of 0 tells it to find an exact match.
Can I use INDEX MATCH with text and numbers together?
Yes. Different criteria can compare text, numbers, or dates, provided the source values and lookup values are compatible. Inconsistent types, spaces, and time portions in dates are common reasons an apparently correct lookup does not match.
Should I use a helper column or multiply the criteria?
Use the direct array formula when you want a compact lookup without adding a data column. Use a helper column when a visible combined key is easier for your team to maintain, while taking care to avoid ambiguous concatenated values.
Related
- Excel Format as Number: Steps and Common Fixes
- Excel Format by Number Range with Conditional Formatting
- Excel Find and Delete Text: Replace, Clear, or Remove
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Weighted Average Calculator in Excel: Formula & ExamplesTo calculate a weighted average in Excel, multiply each value by its corresponding weight, add those products, and…
- Excel FILTER Formula for Multiple Criteria and ResultsTo filter rows by multiple criteria in Excel, use the FILTER function and combine conditions with multiplication for…
- Filter Excel Table by Color: Steps and TroubleshootingYes. In Excel for Microsoft 365 on Windows, open the filter arrow in the table column containing the…
- Excel FLOOR Function: How to Round Decimal Values DownTo round a decimal down in Excel, use FLOOR.MATH with the increment you want. With a value of…
- Excel VAT Formula at 20%: Add or Remove VATIf A2 contains a price before VAT, calculate VAT at 20% with =A2*20%; add that VAT to the…
- How to Change the Year in an Excel Calendar FormulaTo change the year in an Excel calendar formula, update the year cell the calendar refers to; the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.