If your table has Product in column B, Region in C, Sales in D, and a combined lookup key in A, use this exact-match formula to look up both criteria from F2 and G2: =VLOOKUP(F2&"|"&G2,$A$2:$D$100,4,FALSE). VLOOKUP accepts one lookup value, so the usual solution is to join the criteria into one key, either in a helper column or in a formula-created array.
Why VLOOKUP needs a combined lookup value
VLOOKUP searches for one value in the first column of a table and returns a value from a column to its right. It does not have separate arguments for conditions such as “Product is Keyboard” and “Region is East.” Combining those conditions creates a single value that VLOOKUP can search for—for example, Keyboard|East.
This is a lookup, not a calculation across every matching row. If more than one row has the same combination of criteria, VLOOKUP returns the first match it encounters from top to bottom. If you need to add the sales for all matching rows, use SUMIFS instead; if you need every matching record, use FILTER in a supported Excel version.
Build a helper column step by step
A helper column is usually the clearest and easiest-to-maintain method, especially in a large worksheet or one used by people with different Excel versions. For this example, the source table has a key in column A, Product in B, Region in C, and Sales in D. Put the lookup criteria in F2 (Product) and G2 (Region).
- Enter a heading such as LookupKey in A1. Keep the lookup key in the leftmost column of the VLOOKUP table.
- In A2, combine the Product and Region from B2 and C2, then fill the formula down through the source rows.
- Enter the requested Product in F2 and Region in G2.
- Use VLOOKUP with the complete table range, the Sales column number, and FALSE for an exact match.
With Product in B2:B100, Region in C2:C100, and a new helper key in A2:A100, enter this in A2 and fill down:
=B2&"|"&C2
With the criteria in F2 and G2 and Sales in the fourth column of A:D, enter this lookup formula:
=VLOOKUP(F2&"|"&G2,$A$2:$D$100,4,FALSE)
For example, if a row contains Keyboard in B2, East in C2, and 120 in D2, its key is Keyboard|East. If F2 is Keyboard and G2 is East, the formula returns 120. The dollar signs keep the source range fixed when you copy the lookup formula to other cells. Change the ending row to match your data, or use an Excel Table to make the reference expand as rows are added.
Choose a safe separator and consistent values
The separator between criteria matters. Simply joining values can create ambiguous keys: the pair AB and C produces ABC, as does A and BC. A character such as a vertical bar often makes the key easier to read, but it is safe only if that character cannot appear in the source values in a way that creates a collision. Choose a separator that your data excludes, and use exactly the same construction for the helper key and the lookup criteria.
Pay attention to data types and formatting. A number stored as 7 and text stored as “7” may appear alike but behave differently in some comparisons. IDs with leading zeros are particularly easy to mishandle: if an ID is meant to be 007, preserve it as text rather than allowing Excel to turn it into the number 7. Extra spaces can also prevent a match. If the source data is inconsistent, clean it before building keys; for example, TRIM can remove ordinary extra spaces, but it does not remove every possible invisible character.
If a field can contain the chosen separator, consider a more distinctive separator or construct a key that also encodes each value’s length. The important point is that two different combinations must not accidentally produce the same key. For controlled data such as fixed lists of regions and product names, a separator absent from those fields is often a practical choice.
Use VLOOKUP without adding a worksheet column
In Microsoft 365 for Windows, you can create a two-column lookup array inside the formula with CHOOSE. This avoids changing the source table, though the formula is less transparent to many spreadsheet users. In this example, Product is in B2:B100, Region is in C2:C100, Sales is in D2:D100, and the requested values are in F2 and G2.
Use this formula to join the two source criteria into the virtual first column and pair that with Sales as the second column:
=VLOOKUP(F2&"|"&G2,CHOOSE({1,2},$B$2:$B$100&"|"&$C$2:$C$100,$D$2:$D$100),2,FALSE)
The 2 tells VLOOKUP to return the second column of the array created by CHOOSE, which is the Sales range. FALSE requests an exact match. This approach relies on Excel’s dynamic-array calculation support in Microsoft 365; older Excel releases may require array-entry behavior or may not handle the formula as written. The helper-column method is a better choice when compatibility, auditing, or easy troubleshooting matters.
Why exact match and column numbers matter
Use FALSE for the final VLOOKUP argument when looking up a combined key. It tells Excel to find an exact match rather than an approximate one. Omitting this argument makes VLOOKUP use approximate matching by default, which can return an unintended result when the first column is not sorted appropriately. Combined text keys are generally intended to match exactly.
The column index is counted from the first column of the selected table range, not from the worksheet. In the helper-column example, the range A:D has four columns, so Sales in D is index 4. If the selected range were A:C, index 4 would be outside the range and produce an error. Keep the range and the index in sync if you move or add columns.
For an exact match with a helper key, errors such as #N/A usually mean no identical key was found. You can show a more reader-friendly message for that case. With the same A:D table and criteria in F2 and G2, use:
=IFNA(VLOOKUP(F2&"|"&G2,$A$2:$D$100,4,FALSE),"No match")
This displays No match when VLOOKUP returns #N/A. It does not hide other errors, which can be useful because errors such as an invalid column index may indicate a problem in the formula rather than missing data.
Find duplicates, sum matches, or return all matches
A lookup formula is not a duplicate check. If two rows share the same combined key, VLOOKUP returns the value from the first matching row. Sort order can therefore affect which result appears. If a key should be unique, check that assumption—for example, by using conditional formatting to identify duplicates in the helper-key column or by reviewing the source data before relying on the result.
Choose a different function when the desired result is not “the value from the first matching row”:
- To add values for every matching Product and Region: with Product in B2:B100, Region in C2:C100, Sales in D2:D100, and criteria in F2 and G2, use
=SUMIFS($D$2:$D$100,$B$2:$B$100,F2,$C$2:$C$100,G2). For Keyboard and East rows with sales of 120 and 30, the result is 150. - To return every matching row: FILTER is available in Microsoft 365 and Excel 2021 or later. With the same source columns and criteria, use
=FILTER(B2:D100,(B2:B100=F2)*(C2:C100=G2),"No match"). It returns matching Product, Region, and Sales rows, rather than just the first one. - To return one value using a modern lookup function: XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It can test multiple conditions directly, as described below.
When XLOOKUP or INDEX and MATCH is a better fit
If you use Microsoft 365 or Excel 2021 and later, XLOOKUP can match multiple conditions without a helper column or CHOOSE. With Product in A2:A100, Region in B2:B100, Sales in C2:C100, and criteria in F2 and G2, use:
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"No match",0)
Each comparison produces TRUE or FALSE values; multiplying the two tests produces 1 only where both criteria are true. XLOOKUP searches for that 1 and returns the corresponding Sales value. The 0 specifies exact matching. As with VLOOKUP, the first matching row is returned if the criteria are duplicated. Unlike VLOOKUP, XLOOKUP can return values from a column to the left of the lookup range, but it is not available in Excel 2019 or earlier.
INDEX and MATCH can also handle multiple criteria in supported Excel versions, but the syntax can be less approachable and older releases may require special array-entry steps. For many workbooks, a helper key with VLOOKUP is simpler to explain and maintain. The best option depends on compatibility, whether you need one result or all results, and whether adding a helper column is acceptable.
Common problems and practical checks
If a formula returns #N/A unexpectedly, compare the complete lookup key with the helper key character by character. Check for leading or trailing spaces, inconsistent punctuation, different spellings, numbers stored as text, and IDs whose leading zeros have been lost. Confirm that the lookup range begins with the helper-key column and includes the column you want returned.
If the formula returns the wrong value rather than an error, check for duplicate keys and verify the column index. Also confirm that the last argument is FALSE; approximate matching is rarely the intended behavior for a combined text key. For a formula copied down a report, use absolute references for the source table, as in $A$2:$D$100, while leaving criteria references such as F2 and G2 relative if they should move with each row.
For a growing list, convert the source range to an Excel Table using Insert > Table. Tables can expand when new rows are added and make column references more readable. You still need a key column for the helper-key approach, and you should verify that formulas fill down into new table rows as expected. In Excel for Mac, the core formulas work the same way; ribbon labels and some interface details can differ. Excel for the web also supports many current formulas, but availability can depend on the account and update channel.
Frequently Asked Questions
Can VLOOKUP use two criteria directly?
Not through separate criteria arguments. Combine the criteria into one lookup value and search a matching combined key, or use a formula-created array. A helper column is often the easiest method to maintain.
Can I use VLOOKUP with three or more criteria?
Yes. Concatenate all criteria in the same order in both the helper key and the lookup value, placing a consistent separator between fields. Each added criterion must be represented on both sides of the lookup.
Why does my multiple-criteria VLOOKUP return #N/A?
The combined lookup value does not exactly match a key in the first column of the table. Check spacing, spelling, separators, data types, and leading zeros, and ensure that the lookup range starts with the key column.
What happens when more than one row matches?
VLOOKUP returns the first matching row in the lookup range. It does not warn that the key is duplicated. Use a uniqueness check, SUMIFS to total matching values, or FILTER to return all matching rows, depending on what you need.
Should I use a helper column or XLOOKUP?
Use a helper column when you value compatibility and a visible, reusable key. Use XLOOKUP for a compact multiple-condition formula if your Excel version supports it. Both return the first match unless you choose a function or method designed to return multiple matches.
Related
- How to Paste an Image in Excel: Steps, Tips, and Fixes
- How to Paste Into Filtered Cells in Excel
- How to Paste Filtered Data in Excel Without Errors
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Excel Criteria: Syntax, Examples, and Common FixesIn Excel, SUMIF criteria tell the function which cells to check before adding matching values. For example, with…
- How to Open Filter in Excel With a Keyboard ShortcutTo turn filters on or off in Excel for Microsoft 365 on Windows, select a cell in your…
- How to Apply a Filter in Excel to Find Data FastTo apply a filter in Excel, click a cell in your data, then choose Data > Filter. Use…
- Excel VLOOKUP IFERROR: Return a Blank for Lookup ErrorsTo make an Excel VLOOKUP display a blank when it returns an error, wrap it in IFERROR: =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"").…
- Excel Time Math: Formulas for Adding and Subtracting HoursExcel time math treats times as fractions of a 24-hour day: subtract an earlier time from a later…
- Excel Filter Dates by Year: Quick Steps and FixesTo filter dates by year in Excel for Microsoft 365 on Windows, open the filter arrow in the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.