Yes—VLOOKUP can look up a row using multiple criteria if you combine those criteria into one lookup key. For example, if the combined key is in column A, the return value is in column D, and the criteria to match are in F2 and G2, use =VLOOKUP(F2&"|"&G2,$A$2:$D$100,4,FALSE). The usual solution is to build that same combined key in a helper column.
Why VLOOKUP needs a combined key
VLOOKUP accepts one lookup value and searches for it in the leftmost column of a table. It does not have separate arguments for conditions such as employee name and region. A practical workaround is to join the criteria into a single text value, such as Alice|West, and search for that value. The key is that the source rows and the lookup request must combine their values in exactly the same order and with the same separator.
For instance, if a table contains the same employee in multiple regions, searching for “Alice” alone cannot identify which record you want. Combining employee and region creates a more specific key. The technique also works for other combinations, such as product and size, customer and invoice number, or date and department. It is not a special multi-criteria mode in VLOOKUP; it is a way to turn several conditions into one searchable value.
Set up a helper column
Suppose the source data has employee names in column B, regions in column C, and sales in column D, with records in rows 2 through 100. Insert or reserve column A for the combined key. In A2, enter the following formula, then fill it down through the source rows:
With employee names in B2:B100 and regions in C2:C100, put this in A2 and copy it down: =B2&"|"&C2.
The resulting key for an Alice record in the West region is Alice|West. In the lookup area, enter the employee name in F2 and the region in G2. With the helper keys in A2:A100 and sales in D2:D100, put this in the result cell, such as H2:
With the requested employee in F2, region in G2, and the source table in A2:D100, use =VLOOKUP(F2&"|"&G2,$A$2:$D$100,4,FALSE).
Here, the first argument builds the requested key, the table range starts with the helper-key column, and 4 tells VLOOKUP to return the fourth column of that range—column D. FALSE requests an exact match. You can copy the result formula down for more lookup requests, provided the source range remains fixed as shown.
Worked example: employee and region
Imagine a small sales list with these three records:
| Key | Employee | Region | Sales |
|---|---|---|---|
| Alice|East | Alice | East | 1250 |
| Alice|West | Alice | West | 900 |
| Bob|West | Bob | West | 1100 |
Assume the key is in A2:A4 and the sales values are in D2:D4. If F2 contains Alice and G2 contains West, the lookup combines them into Alice|West and returns 900. Alice alone would not distinguish the two records, but the combined key does. If the same employee-region pair appears more than once, however, an exact-match VLOOKUP returns the first matching row, not a total or the last record. Check whether the source data has duplicate combinations before relying on the result.
Use VLOOKUP without a helper column
If you do not want to add a key column to the worksheet, CHOOSE can construct a temporary two-column lookup array: one virtual column for the combined criteria and one for the return values. This keeps the original table layout unchanged. With employee names in B2:B100, regions in C2:C100, sales in D2:D100, and lookup criteria in F2 and G2, use:
With those ranges and criteria cells, enter =VLOOKUP(F2&"|"&G2,CHOOSE({1,2},$B$2:$B$100&"|"&$C$2:$C$100,$D$2:$D$100),2,FALSE).
The temporary array has the combined employee-region key in its first column and sales in its second, so the column index is 2. This array-based construction is suitable for current Excel versions that support dynamic arrays, including Microsoft 365 and Excel 2021. In older Excel versions, array evaluation can behave differently and may require an array formula; the helper-column approach is generally easier to inspect and maintain. Use the helper method if colleagues need to understand or troubleshoot the workbook.
More than two criteria
To include a third condition, append it to both versions of the key in the same order. For example, a source key might combine employee, region, and product. The lookup key must use employee, then region, then product too. If the source uses one order and the lookup request another, Excel will not find a match even when all the individual values exist in the table.
With the helper key in A2:A100 built from employee in B, region in C, and product in D, and the return value in E, a corresponding lookup formula could be:
With lookup employee, region, and product in F2, G2, and H2, and the return column in E, use =VLOOKUP(F2&"|"&G2&"|"&H2,$A$2:$E$100,5,FALSE).
Because the table range starts in A and ends in E, E is its fifth column. Adding criteria can make a key more selective, but it cannot resolve duplicate rows where every included field is identical. Decide what should happen with such duplicates—such as returning the first record, summing values, or flagging the data—before choosing a lookup formula.
Prevent key collisions and mismatches
A separator helps distinguish adjacent values. Without one, joining a product code and size could make different pairs produce the same text: AB plus 12 and AB1 plus 2 both become AB12. A separator reduces that risk, but choose one that does not occur in the data. If it might occur, clean or standardize the values first, or use a less likely separator. A helper key is visible, so you can inspect it when a match fails.
- Check spaces: A trailing space in a source value can make its combined key different from the lookup key. TRIM can help standardize ordinary extra spaces, but apply it consistently to both sides.
- Check data types: A number stored as text may not behave like a numeric value formatted as text. Ensure comparable criteria have consistent underlying values and formats.
- Check blanks: A blank criterion still participates in the joined key. Confirm that an empty value is intentional rather than a missing field.
- Check the table range: The helper key must be the leftmost column in the VLOOKUP range, and the column index must count from that first column.
- Use exact match: FALSE avoids approximate-match behavior. Do not omit the fourth argument in a multi-criteria lookup; approximate matching is unsuitable for a concatenated key.
Alternative: INDEX and MATCH with separate conditions
If you want to keep the conditions separate rather than concatenate them, INDEX and MATCH can test each criterion directly. This can be clearer when the source table already has distinct fields and you prefer not to add a helper column. With employees in B2:B100, regions in C2:C100, return values in D2:D100, and the requested employee and region in F2 and G2, use:
With that layout, enter =INDEX($D$2:$D$100,MATCH(1,($B$2:$B$100=F2)*($C$2:$C$100=G2),0)).
The comparisons produce TRUE or FALSE values, which Excel treats as 1 or 0 in the multiplication. A row matching both conditions produces 1, and MATCH finds that row position. In Microsoft 365 and Excel 2021, this formula can be entered normally. In older Excel versions, this type of array calculation may need to be confirmed as an array formula. Like VLOOKUP, this returns the first row meeting the conditions; it does not aggregate multiple matches.
For newer Excel, XLOOKUP can also search a combined key, but it still needs a way to combine the conditions or evaluate them as an array. XLOOKUP is available in Microsoft 365 and Excel 2021, not in older perpetual versions such as Excel 2019. If a workbook must work in older installations, a helper-key VLOOKUP or a compatible INDEX/MATCH formula is often the safer choice.
Frequently Asked Questions
Can VLOOKUP search two columns at once?
Not directly. VLOOKUP searches one lookup value in the first column of its table. Combine the two source criteria into a helper key, or use an array-based formula that creates a combined first column. The criteria and their order must match on both sides.
Why does my multiple-criteria VLOOKUP return #N/A?
The combined lookup key probably does not exactly match a source key. Check spelling, extra spaces, data types, separators, and the order of the criteria. Also confirm that the lookup table includes the helper-key column as its first column and that the lookup value is present.
Should I use TRUE or FALSE for the last VLOOKUP argument?
Use FALSE for an exact match. TRUE requests approximate matching and expects an appropriately sorted lookup column. Concatenated text keys are not a good use case for approximate matching.
Can VLOOKUP return every matching row?
No. VLOOKUP returns one result: the first matching row it finds. To return multiple matching records, use FILTER in Microsoft 365 or Excel 2021, or use another method designed to list multiple results. To calculate a total for all matching records, use a conditional aggregation function such as SUMIFS instead.
Does the helper column have to be visible?
No. You can hide the helper column, but leaving it visible can make the workbook easier to audit and troubleshoot. If you hide it, document how the key is assembled so another user can diagnose mismatches or extend the lookup safely.
Related
- Excel Shortcut to Refresh a PivotTable and All Data
- Excel Shortcut to Select All Data in a Column Quickly
- Excel Shortcut to Select a Whole Column in Seconds
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter by Month in Excel Using Dates or TextTo filter month-wise in Excel, select a cell in your data, choose Data > Filter, open the date…
- Excel Growth Analysis: Formulas, Trends, and ExamplesExcel growth analysis measures how a value changes over time. For two consecutive values in B2 and B3,…
- Excel Conditional Formatting Formula for Due Dates ExplainedIn Excel for Microsoft 365 on Windows, select the cells to format, choose Home > Conditional Formatting >…
- Excel IFERROR: How It Works, Examples, and Common UsesExcel’s IFERROR checks a value or formula and returns that result if it is not an error; if…
- Copy Unique Values in Excel: A Step-by-Step GuideTo copy distinct values from a list in Excel for Microsoft 365, enter =UNIQUE(A2:A100) in an empty cell,…
- Excel Weighted Average Using SUMPRODUCT: Formula and ExamplesTo calculate a weighted average in Excel, multiply each value by its weight, add those products, then divide…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.