Yes, you can use INDEX and MATCH with multiple criteria in Excel without entering it as an array formula (i.e., without pressing Ctrl+Shift+Enter) by using the MATCH function with a concatenated lookup value and a concatenated lookup array, or by using the newer XLOOKUP function. The classic non-array approach relies on helper columns or the & operator to combine criteria into a single lookup key.
Why the Array Formula Problem Exists
In older Excel versions (before dynamic arrays in Excel 365), a standard formula like =INDEX(C2:C100, MATCH(1, (A2:A100="Red") * (B2:B100="Large"), 0)) requires you to press Ctrl+Shift+Enter because the MATCH function is evaluating an array of Boolean values. Without array entry, Excel would either return an error or an incorrect result. Many users find array formulas confusing and prone to mistakes, especially when editing or copying them. The non-array alternatives below avoid this complexity entirely.
Non-Array Method 1: Helper Column
The simplest and most compatible method is to create a helper column that combines your criteria into a single unique identifier. For example, if you have product names in column A, sizes in column B, and prices in column C, you could put in column D (say D2) the formula =A2&"|"&B2 and copy it down. Then, to find the price for “Red” and “Large”, you use a regular INDEX/MATCH:
Assumes data in A2:C100, helper column D, and lookup values in F2 (color) and G2 (size).
=INDEX(C2:C100, MATCH(F2&"|"&G2, D2:D100, 0))
This works in any Excel version and does not require array entry. The pipe symbol (|) is used as a separator to avoid accidental matches when concatenating values like “Red” and “Large” vs. “RedL” and “arge”. You can use any character that doesn’t appear in your data.
Non-Array Method 2: Concatenate Inside MATCH
If you prefer not to add a helper column, you can concatenate the criteria directly inside the MATCH function, but you must also concatenate the entire lookup array. This requires array entry in older Excel, but in Excel 365 it works as a dynamic array formula without Ctrl+Shift+Enter. For example:
Assumes data in A2:C100, with criteria in F2 and G2.
=INDEX(C2:C100, MATCH(F2&"|"&G2, A2:A100&"|"&B2:B100, 0))
In Excel 365, this formula will spill correctly without special entry. In Excel 2019 and earlier, you must press Ctrl+Shift+Enter to make it an array formula. If you are using an older version and want to avoid array entry, the helper column method is the safest bet.
Using XLOOKUP for Multiple Criteria (Non-Array)
If you have Excel 2021 or Microsoft 365, the XLOOKUP function is the modern replacement for INDEX/MATCH. It handles multiple criteria natively without any array entry or helper columns. The syntax is:
=XLOOKUP(1, (A2:A100=F2) * (B2:B100=G2), C2:C100)
This works because the (A2:A100=F2) * (B2:B100=G2) creates an array of 1s and 0s, and XLOOKUP is designed to handle array operations without requiring Ctrl+Shift+Enter. It returns the first match. XLOOKUP is not available in Excel 2019 or earlier, so for older versions you’d stick with the helper column.
How to Choose Between the Methods
- Excel 365 or 2021: Use XLOOKUP for simplicity and no helper columns.
- Excel 2019 or older: Use a helper column to avoid array formulas entirely.
- If you must avoid helper columns and use older Excel: You can use the concatenated MATCH with Ctrl+Shift+Enter, but be aware of the array formula limitations.
- If your criteria are numeric and you need exact matches: All methods work; just ensure your data types are consistent.
Common Problems and Solutions
- #N/A error: This means no exact match was found. Check that your criteria exist in the data and that there are no extra spaces (use TRIM).
- #VALUE! error: In older Excel, this often happens when you forget to press Ctrl+Shift+Enter for an array formula. Switch to a helper column.
- False matches due to concatenation: If you concatenate without a separator, “RedLarge” could match “Red” and “Large” incorrectly. Always use a unique separator like
"|". - Multiple matches: INDEX/MATCH returns the first match. If you need all matches, use FILTER (Excel 365) or a pivot table.
- Case sensitivity: MATCH is case-insensitive by default. If you need case-sensitive matching, use EXACT in an array formula or XLOOKUP with the match_mode argument set to 0 (exact match) and use EXACT within the criteria.
Comparison: INDEX/MATCH vs. XLOOKUP vs. VLOOKUP
VLOOKUP is the simplest but has severe limitations: it can only look to the right, requires a sorted table for approximate matches, and cannot easily handle multiple criteria without helper columns. INDEX/MATCH is more flexible and works in all Excel versions, but with multiple criteria it either needs a helper column or array entry. XLOOKUP is the best choice for modern Excel users: it handles multiple criteria, works left and right, has built-in error handling, and doesn’t require array entry. If you are sharing a workbook with users on older Excel, stick with the helper column method to ensure compatibility.
Frequently Asked Questions
Can I use INDEX/MATCH with multiple criteria without Ctrl+Shift+Enter in Excel 2019?
Yes, but only if you use a helper column. The direct concatenation method inside MATCH requires array entry in Excel 2019 and earlier. The helper column method uses regular formulas and works in any version.
What is the best separator to use when concatenating criteria?
Use a character that is unlikely to appear in your data, such as a pipe |, a tilde ~, or a special character. Avoid using a space or a hyphen, as those might naturally appear in your text and cause false matches.
Does XLOOKUP work for multiple criteria in Excel 2019?
No, XLOOKUP is only available in Excel 2021 and Microsoft 365. In Excel 2019, you need to use INDEX/MATCH with a helper column or an array formula.
How do I return the second match instead of the first?
INDEX/MATCH and XLOOKUP both return the first match by default. To get the second match, you can use the SMALL function with an array formula, or use FILTER (Excel 365) and then INDEX. A simpler approach is to sort your data so the desired match appears first.
Can I use multiple criteria with wildcards in INDEX/MATCH?
Yes, you can use wildcards like * and ? in the lookup value, but only if you use the MATCH function with match_type 0 and the lookup value is text. For example, =INDEX(C2:C100, MATCH("Red*", A2:A100, 0)) will match any cell starting with “Red”. For multiple criteria with wildcards, you may need to use an array formula or a helper column with a wildcard-friendly approach.
Final Tips for Non-Array Multiple Criteria Lookups
Always test your formulas with a small dataset first. If you are using a helper column, make sure it is formatted as text if your criteria are text. For large datasets, helper columns can slow down calculation, but they are still faster than array formulas in many cases. If you are using Excel 365, consider using the FILTER function to return all matching rows, which is often more intuitive than INDEX/MATCH.
Related
- Excel Group Lake Jackson TX: What It Is & Services
- Excel Flexi Timesheet Template UK: How to Build One
- Excel First Letter Uppercase Rest Lowercase?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDEX Array Formula: How It Works and When to Use ItIn Excel for Microsoft 365, INDEX can return a value from a range or return an array of…
- SEQUENCE in Excel: Build Number Series AutomaticallyIn Excel for Microsoft 365, use the SEQUENCE function to create a series of numbers that spills into…
- Excel Formula for Weeks Between Two Dates: Full WeeksTo calculate weeks between two dates in Excel, subtract the start date from the end date and divide…
- Excel Combine Unique Lists: 3 WaysTo combine two or more lists in Excel and keep only unique values, use the UNIQUE function with…
- Split Function Renal Scan: Purpose and ResultsA split function renal scan is a nuclear medicine test that estimates how much each kidney contributes to…
- How to Extract One Word From a Cell in Excel With FormulasTo extract a particular word from text in Excel for Microsoft 365, put the text in A2 and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.