The best way to do a VLOOKUP with multiple criteria in Excel is to use the INDEX and MATCH functions together, because VLOOKUP alone cannot handle more than one lookup value. The formula is =INDEX(return_range, MATCH(1, (criteria1_range=criteria1) * (criteria2_range=criteria2), 0)) entered as an array formula (or with dynamic arrays in Excel 365).
Why VLOOKUP Falls Short with Multiple Criteria
VLOOKUP looks for a single value in the leftmost column of a table and returns a value from a column to the right. It has three built-in limitations that make it unsuitable for multiple criteria:
- It can only match one lookup value.
- It requires the lookup column to be the first column in the range.
- It returns the first match found, not necessarily the one you want if duplicates exist.
When you need to match on two or more conditions—for example, finding a sales figure for a specific product in a specific region—VLOOKUP simply has no argument for the second condition. You could concatenate the criteria into a helper column, but that adds a column to your data and is less flexible than a formula-based approach.
How INDEX and MATCH Work Together
INDEX returns the value at a given row and column position in a range. MATCH returns the position of a lookup value within a row or column. Combined, MATCH finds the row number and INDEX pulls the value from that row.
For multiple criteria, you create an array of 1s and 0s by multiplying logical tests together. Each test checks one criterion. The multiplication acts like an AND: a row gets a 1 only if all tests are true. MATCH then finds the position of the first 1.
Example: you have a table with products in column A, regions in column B, and sales in column C. To find sales for “Widget” in “East”:
- Assume data in A2:C100.
- Put “Widget” in E1 and “East” in E2.
- Formula:
=INDEX(C2:C100, MATCH(1, (A2:A100=E1) * (B2:B100=E2), 0))
In Excel 365, this formula works normally because dynamic arrays handle the intermediate array. In older Excel versions, you must press Ctrl+Shift+Enter to enter it as an array formula—curly braces will appear around it.
Using XLOOKUP as a Modern Alternative
If you are using Excel 2021 or Microsoft 365, XLOOKUP is a simpler and more powerful alternative. It can handle multiple criteria by concatenating lookup values and lookup arrays with the ampersand (&).
Using the same example:
=XLOOKUP(E1&E2, A2:A100&B2:B100, C2:C100)
This formula concatenates the two criteria into one lookup value and concatenates the two columns into one lookup array. It requires no Ctrl+Shift+Enter and is easier to read. However, it still returns the first match, and if you need to return multiple matches, you would use the FILTER function instead.
Common Errors and How to Fix Them
When using INDEX-MATCH with multiple criteria, you may encounter these issues:
- #N/A error: No row meets all criteria. Check that your criteria exist and that there are no extra spaces or text mismatches.
- #VALUE! error: Often caused by entering the formula without Ctrl+Shift+Enter in older Excel versions.
- Wrong result: This can happen if your criteria ranges are not the same size, or if you accidentally use commas instead of multiplication signs.
- Case sensitivity: INDEX-MATCH is not case-sensitive by default. If you need case-sensitive matching, you must use the EXACT function inside the formula:
=INDEX(C2:C100, MATCH(TRUE, EXACT(A2:A100,E1)*EXACT(B2:B100,E2), 0))(also an array formula).
To debug, evaluate the formula step by step with Formulas > Evaluate Formula, and check that the array of 1s and 0s looks as expected.
How to Choose Between VLOOKUP, INDEX-MATCH, and XLOOKUP
Your choice depends on your Excel version and your specific needs:
- VLOOKUP: Use only for single-criteria lookups where the lookup column is the leftmost and you are okay with the first match.
- INDEX-MATCH: Use when you need multiple criteria, when your lookup column is not the leftmost, or when you want to avoid a helper column. Works in all Excel versions.
- XLOOKUP: Use if you have Excel 2021 or 365 and want a simpler formula. It also allows you to specify a not-found message and can return multiple matches if needed.
- FILTER: Use when you need to return all matching rows, not just the first one.
For most users on modern Excel, XLOOKUP is the easiest. For backward compatibility, INDEX-MATCH is the standard.
Real-World Example: Employee Lookup
Suppose you have employee data with Department in column A, Job Title in column B, and Salary in column C. You want to find the salary of an employee in “Sales” with the title “Analyst”.
With INDEX-MATCH:
=INDEX(C2:C100, MATCH(1, (A2:A100="Sales") * (B2:B100="Analyst"), 0))
With XLOOKUP:
=XLOOKUP("Sales"&"Analyst", A2:A100&B2:B100, C2:C100)
Both return the first salary that matches both conditions. If you need to find all such salaries, use:
=FILTER(C2:C100, (A2:A100="Sales") * (B2:B100="Analyst"))
Frequently Asked Questions
Can I use VLOOKUP with multiple criteria by adding a helper column?
Yes. You can create a helper column that concatenates the criteria, for example =A2&"|"&B2, and then use VLOOKUP with the concatenated lookup value. This works but adds a column to your data and can be less efficient.
Why does my INDEX-MATCH formula return #N/A even though the data exists?
This usually happens because of leading/trailing spaces, text stored as numbers, or because you pressed Enter instead of Ctrl+Shift+Enter in older Excel versions. Use TRIM and TEXT functions to clean data, and ensure you enter the formula correctly.
Does INDEX-MATCH work in Google Sheets?
Yes, the same formula works in Google Sheets with the same syntax. However, Google Sheets automatically handles array formulas, so you don’t need Ctrl+Shift+Enter. XLOOKUP is also available in Google Sheets.
What is the difference between INDEX-MATCH and XLOOKUP for multiple criteria?
XLOOKUP is simpler and more readable, but it requires Excel 2021 or 365. INDEX-MATCH works in all versions and is more flexible for array operations. Both return the first match; for multiple matches, use FILTER.
Can I use multiple criteria with VLOOKUP in Excel 365?
VLOOKUP still cannot handle multiple criteria natively, even in Excel 365. You must use a helper column or switch to INDEX-MATCH or XLOOKUP.
Tips for Large Data Sets and Performance
When working with thousands of rows, array formulas like INDEX-MATCH can slow down your workbook because they calculate over entire columns. To improve performance:
- Limit the ranges to the actual data size, e.g.,
A2:A1000instead ofA:A. - Use Excel Tables (Insert > Table) so ranges adjust automatically.
- In Excel 365, XLOOKUP and FILTER are generally faster than array-entered INDEX-MATCH.
- Consider using Power Query for complex multi-criteria lookups on very large data sets.
Also, if you are using Excel on a Mac, the keyboard shortcut for array formulas is Cmd+Shift+Enter instead of Ctrl+Shift+Enter.
Related
- Excel Template to Pay Off Debt? Yes, Here’s How
- Excel Inventory Template: How to Track Stock
- Excel Template Job Schedule: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Align Left Shortcut: Windows and Mac GuideIn Excel for Microsoft 365 on Windows, select the cell or range and press Alt, then H, A,…
- Excel Let Text Spill Into the Next Cell? How It WorksYes. In Excel for Microsoft 365, text can appear to spill into the cell to its right when…
- Compare Two Lists in Excel to Find Unique ValuesTo find values in one Excel list that are missing from another, use FILTER with XMATCH; wrap the…
- Excel Filter Between Two Dates: Steps and Common FixesTo filter Excel dates between two limits, open the filter arrow in the date column, choose Date Filters…
- Excel Formula to Calculate Your Age from Birth DateIf the birth date is in cell A2, enter =DATEDIF(A2,TODAY(),"Y") to calculate age in completed years as of…
- Excel Protect Sheet: What to Do If You Forgot the PasswordIf you forgot an Excel worksheet password, Microsoft Excel has no built-in way to reveal or reset it:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.