The simplest way to do a VLOOKUP with multiple criteria in Excel is to add a helper column that combines the criteria into one lookup value, then use that column as the lookup column. For example, with product code in A2:A20 and color in B2:B20, put =A2&"-"&B2 in C2 and drag down, then use =VLOOKUP("P01-Red", C2:D20, 2, FALSE). This works in every Excel version. For a no-helper-column option, use XLOOKUP with the ampersand operator: =XLOOKUP("P01"&"-"&"Red", A2:A20&"-"&B2:B20, D2:D20) (needs Excel 2021 or Microsoft 365).
Why VLOOKUP Can’t Handle Multiple Criteria Directly
VLOOKUP is designed to find a single value in the first column of a table and return a value from a column to the right. Its syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The lookup_value must be a single value or a cell reference—it cannot accept two separate columns as a condition. If you try to use two criteria, you either get a #N/A error (because no single row matches both) or, worse, a wrong result if the first criterion matches but the second doesn’t. The standard workaround is to create a unique key that combines the criteria, making each row identifiable by that key.
Method 1: Helper Column with Concatenation
The classic approach adds a new column (often the first column of the table) that joins the criteria with a separator. Steps:
- Insert a new column to the left of your data (or use an existing empty column).
- In the first data row, enter a formula like
=A2&"-"&B2(replace A2 and B2 with your actual criteria columns). - Copy the formula down for all rows.
- Use
VLOOKUPwith the combined key:=VLOOKUP("P01-Red", A2:D20, 4, FALSE)where column A is the helper and column D holds the return value.
Use a separator like a hyphen or space that cannot appear naturally in your data to avoid false matches. For example, if your product codes contain hyphens, use a pipe symbol: =A2&"|"&B2. This method works in all Excel versions, including Excel 2007 and later, and is easy to troubleshoot because you can see the combined keys.
Method 2: XLOOKUP with Array Concatenation
If you have Excel 2021 or Microsoft 365, XLOOKUP can handle multiple criteria without a helper column. The formula uses the ampersand to build an array of combined lookup values and a corresponding array of combined criteria. Example: with product code in A2:A20, color in B2:B20, and price in D2:D20, enter:
=XLOOKUP("P01"&"-"&"Red", A2:A20&"-"&B2:B20, D2:D20)
This returns the price for the row where A equals “P01” and B equals “Red”. XLOOKUP is more flexible than VLOOKUP: it allows lookups to the left, returns an exact match by default, and handles arrays natively. On Excel for the web and Excel for Mac (Microsoft 365), the same formula works. If you need to return multiple columns, use =XLOOKUP(... , ... , D2:E20) and it spills the result.
Method 3: INDEX and MATCH with Multiple Criteria
Before XLOOKUP, INDEX/MATCH was the go-to for multiple criteria. It works in all Excel versions. The formula uses MATCH with an array expression to find the row number, then INDEX to return the value from that row. Example: with criteria in G1 and H1, and data in A2:A20 (product), B2:B20 (color), D2:D20 (price):
=INDEX(D2:D20, MATCH(1, (A2:A20=G1)*(B2:B20=H1), 0))
This is an array formula. In Excel 2019 and earlier, you must press Ctrl+Shift+Enter to enter it. In Excel 2021 and Microsoft 365, it works with a normal Enter because dynamic arrays are enabled. The (A2:A20=G1)*(B2:B20=H1) part creates an array of 1s and 0s; only the row where both conditions are true gets a 1. MATCH finds that 1. This method is powerful but less intuitive than XLOOKUP for beginners.
Method 4: FILTER Function (Microsoft 365)
If you need to return all matching rows, not just one value, use the FILTER function. It returns an array of all rows that meet your criteria. Example: to get all prices for product “P01” and color “Red”:
=FILTER(D2:D20, (A2:A20="P01")*(B2:B20="Red"), "No match")
This spills the results down the column. If there are multiple matches, you get multiple prices. FILTER is ideal for reports and dashboards. It requires Microsoft 365 or Excel 2021. On Excel for the web, it works too. Note that FILTER returns a dynamic array, so ensure you have empty cells below to avoid a #SPILL! error.
Common Problems and How to Fix Them
- #N/A error: The combined key does not exist. Check for extra spaces, inconsistent data types (text vs. number), or wrong separator. Use
=TRIM()to clean data. - Wrong result: The helper column separator appears in the data, causing false matches. Use a unique separator like
"|"or"~". - #VALUE! error with INDEX/MATCH: You forgot to press Ctrl+Shift+Enter in older Excel versions, or the arrays are not the same size.
- #SPILL! error with FILTER: Something is in the way of the spilled range. Clear the cells below or move the formula.
- Data type mismatch: If one criterion is a number stored as text, the match fails. Convert with
=VALUE()or use=TEXT()to normalize.
Comparing the Methods: Which to Choose?
Your choice depends on your Excel version and your needs:
- Helper column + VLOOKUP: Works everywhere, easy to understand, but adds a column to your data and requires manual updates if you add rows.
- XLOOKUP: Best for Microsoft 365 users; no helper column, clean, and supports arrays. Ideal for most modern workbooks.
- INDEX/MATCH: Good for legacy versions and when you need to look to the left. Slightly more complex syntax.
- FILTER: Use when you need multiple matches or want to return a whole array. Not for single lookups.
For a quick one-off lookup, XLOOKUP is the fastest to write. For compatibility with older files, use the helper column method. If you are building a template for others, the helper column is the most transparent.
Frequently Asked Questions
Can I use VLOOKUP with two columns without a helper column?
No, VLOOKUP cannot directly use two criteria. You must either use a helper column, or switch to XLOOKUP, INDEX/MATCH, or FILTER. The helper column is the only way to keep VLOOKUP itself.
How do I do a VLOOKUP with three criteria?
Use the same methods but concatenate three columns. In a helper column: =A2&"-"&B2&"-"&C2. With XLOOKUP: =XLOOKUP("A"&"-"&"B"&"-"&"C", A2:A20&"-"&B2:B20&"-"&C2:C20, D2:D20). The logic scales to any number of criteria.
Why does my VLOOKUP return #N/A even though the criteria match?
Often it’s a formatting issue: numbers stored as text, leading/trailing spaces, or invisible characters. Use =TRIM() on the criteria and the lookup column. Also ensure the helper column uses the same separator as in the lookup value.
Does XLOOKUP work in Excel 2019?
No, XLOOKUP was introduced in Excel 2021 and Microsoft 365. In Excel 2019, use INDEX/MATCH or a helper column with VLOOKUP.
Can I use a helper column on a different sheet?
Yes, the helper column can be anywhere, as long as the table array in VLOOKUP includes it as the first column. For example, you can put the helper column in column Z of the same sheet and reference Z2:AA20.
Is there a way to make the helper column dynamic?
Yes, use an Excel Table (Insert > Table). When you add a formula in a new column, it automatically fills down for new rows. This keeps your VLOOKUP working as data grows.
Related
- Excel Template for Family Budget: How to Build One
- Excel Template for HOA Budget: How to Build One
- Excel Template for Event Budget: 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
- COUNTIF Excel Formula for Dates: Examples and FixesTo count cells that contain a particular date, use Excel’s COUNTIF function with a real date value as…
- Break-Even Excel Formula: Calculate Units and SalesThe basic break-even formula in Excel is fixed costs divided by the contribution margin per unit: =B2/(B3-B4). If…
- Excel SUM Array: Formulas, Examples, and Common FixesTo add the values in an array or range in Excel, use =SUM(A2:A10) when the numbers are in…
- Excel 365 Table Formulas: Structured References ExplainedExcel 365 table formulas are formulas entered in an Excel table that can use readable column names, such…
- LEFT in Excel: Extract Text from the StartIn Excel, use the LEFT function to return characters from the beginning of a text value: with text…
- Excel Formula for Next Year Calendar?The simplest Excel formula for a next-year calendar is =DATE(YEAR(TODAY())+1,1,1), which returns the first day of next year…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.