Yes. To use VLOOKUP with two criteria, combine the criteria into one helper key, then look up that key with an exact match. VLOOKUP accepts one lookup value, so the helper key lets you represent a pair of values as a single value without changing the basic lookup method.
Why VLOOKUP needs a combined key
VLOOKUP searches for one value in the leftmost column of a table array. It does not have separate arguments for two conditions, such as product and region. If you look up only the product, Excel may find the product in the wrong region; if you look up only the region, it may return the wrong product.
The common workaround is to join the two criteria into a key in a new column. For example, “Widget” and “East” become “Widget|East.” The pipe character is just a separator that makes the two parts easier to distinguish. VLOOKUP then searches for that combined key and returns the corresponding value from a column to its right.
This approach is useful when a record is identified by a combination of fields: customer and invoice number, employee and month, or product and location. It does not make VLOOKUP evaluate two conditions independently; it makes the two values into one lookup value.
Set up a helper-column example
Suppose a small price list has product names in column B, regions in column C, and prices in column D. Put a helper key in column A, to the left of those columns, so VLOOKUP can search it and return a value to its right. Use these example records:
- Widget, East, 12
- Widget, West, 14
- Cable, East, 5
- Cable, West, 6
With the item in B2 and the region in C2, enter this key formula in A2, then fill it down through the data rows. For the first example record, the result is Widget|East.
=B2&"|"&C2
Place the lookup criteria in F2 and G2: enter Widget in F2 and East in G2. With the combined keys in A2:A5, the original fields in B:D, and the criteria in F2:G2, this VLOOKUP formula returns 12:
=VLOOKUP($F$2&"|"&$G$2,$A$2:$D$5,4,FALSE)
The fourth argument, FALSE, requests an exact match. The 4 tells VLOOKUP to return the fourth column of the table array A:D, which is column D, the price. The dollar signs keep the criteria cells and lookup range fixed if you copy the formula elsewhere. Adjust the row numbers to cover your actual data.
Build and use the formula step by step
- Insert a helper column to the left of the return data, or otherwise make sure the helper key is the first column in the VLOOKUP table array.
- Combine the two source fields in the helper column using the same separator each time. Fill the formula down for every record.
- Enter the two search criteria in separate cells, such as F2 and G2.
- Join those criteria in the lookup value, and use the helper-key column as the first column of the table array.
- Use the correct return-column number and FALSE for an exact match. Test a known pair before copying the formula into a report.
Column numbers are counted from the start of the table array, not from the worksheet. In A:D, A is column 1 and D is column 4. If you narrow the lookup range to A:D, the formula still uses 4 for the price. If you start the range at A and end at C, a return index of 4 would be outside the range and produce an error.
Make the combined key unambiguous
A separator works when it cannot be confused with part of either criterion. If product names or regions might themselves contain a pipe, two different pairs could create the same joined text. For example, values containing the separator can make a key built with a simple join ambiguous. Pick a separator that is not present in the source values, or use a length-prefixed key instead.
With the item in B2 and region in C2, this alternative key formula records the length of each value as well as the value. For Widget and East, it returns 6:Widget4:East. Use the same construction for the lookup criteria to ensure the keys are built consistently.
=LEN(B2)&":"&B2&LEN(C2)&":"&C2
With the criteria in F2 and G2, the matching lookup value can be built directly inside VLOOKUP. If column A contains the length-prefixed keys and column D contains the prices, this formula returns 12 for Widget and East:
=VLOOKUP(LEN(F2)&":"&F2&LEN(G2)&":"&G2,$A$2:$D$5,4,FALSE)
Whatever key format you choose, use the same logic for both the source rows and the search criteria. Be particularly consistent with dates, numbers, and text: joining values turns the result into text, and values that look similar on screen can have different underlying representations. For example, the number 1 and text “01” do not necessarily produce the same key.
Duplicates, exact matches, and missing results
VLOOKUP returns the first matching key it encounters from the top of the lookup range. If the same product-region pair appears more than once, the formula does not decide which record is more appropriate; it returns the first one. Check whether duplicate pairs are valid, or whether you need to add another criterion, such as date or transaction ID.
Use FALSE for the fourth argument when the key must match exactly. Leaving that argument out, or using TRUE, can lead to approximate-match behavior, which is generally unsuitable for a combined text key. Approximate matching also relies on data being sorted appropriately. For ordinary two-criteria lookups, explicitly writing FALSE makes the intent clear.
If Excel displays #N/A, it has not found the combined key. Check that both criteria are spelled and formatted like the source fields, that the helper formula has been filled down, and that the lookup range includes the relevant rows. Extra spaces can be hard to spot; a leading space in “Widget” makes a different text value from “Widget” without one. You can inspect the source cells or clean inconsistent source data before relying on the result.
Alternatives when you do not want a helper column
In Microsoft 365 and Excel 2021, XLOOKUP can evaluate two conditions in one formula by multiplying two TRUE/FALSE tests. With products in B2:B5, regions in C2:C5, prices in D2:D5, and criteria in F2 and G2, this formula returns 12 for Widget and East. If there is no match, it returns the text Not found.
=XLOOKUP(1,(B2:B5=F2)*(C2:C5=G2),D2:D5,"Not found")
Each comparison produces TRUE or FALSE values; multiplying the comparisons turns rows where both tests are TRUE into 1. XLOOKUP searches for 1 and returns the corresponding price. This can be more direct than a helper key, but it is not a VLOOKUP formula and is unavailable in older Excel versions such as Excel 2019 and Excel 2016.
INDEX and MATCH can also handle two conditions without adding a worksheet column. With the same layout, this formula returns 12 in Microsoft 365:
=INDEX($D$2:$D$5,MATCH(1,($B$2:$B$5=$F$2)*($C$2:$C$5=$G$2),0))
In older Excel versions that do not calculate this kind of array formula automatically, confirm it with Ctrl+Shift+Enter rather than Enter; Excel may display braces around the formula. Do not type those braces yourself. On Mac, the equivalent array-entry shortcut is Control+Shift+Return. In current Microsoft 365 Excel for Windows, Enter is sufficient.
These alternatives are useful when you cannot rearrange a table to put a helper column first, or want to avoid maintaining one. If a workbook must work in older Excel versions, the helper-column VLOOKUP is often easier to understand and broadly compatible. XLOOKUP and dynamic-array behavior vary by Excel version, so check the version used by everyone who will open the file.
Common errors and practical checks
- Wrong return index: Count columns from the first column of the table array. If the array begins at A, column D is 4.
- Helper key is not first: VLOOKUP cannot search to the left of its lookup column. Move the helper key to the left of the return column or use an alternative function.
- Only one side uses a separator: The lookup value and source key must be constructed the same way, character for character.
- Unintended approximate match: Include FALSE as the last argument for an exact match.
- Criteria look equal but differ: Check spaces, numbers stored as text, and date values with different formats or underlying values.
- Formula range misses new rows: Expand the range when adding records, or use an Excel Table so the data range can grow with the table.
For text criteria that may contain wildcard characters such as an asterisk or question mark, remember that VLOOKUP can interpret those characters as wildcards even when the fourth argument is FALSE. If literal wildcard characters are part of your data, use a method that treats the criteria literally or escape them appropriately before using the lookup. This is an unusual case, but it can explain a result that appears to match more broadly than expected.
Keep the lookup maintainable
For a small, stable list, ordinary cell references are straightforward. For a list that grows, formatting the range as an Excel Table can make formulas easier to extend and read. On Windows, select a cell in the data and choose Insert > Table, then confirm whether the table has headers. The Table Design tab lets you set or change the table name. Mac users have the same Insert > Table route in current Excel, though ribbon layout can vary by release.
A helper column is not wasted work: it makes the matching logic visible and lets you inspect exactly which key each row uses. Label it clearly, such as Lookup key, and avoid manually typing keys into the cells if a formula can generate them. When reviewing a workbook, check that the key formula is consistent through the full dataset and that the return column is the one intended.
Frequently Asked Questions
Can VLOOKUP use two criteria directly?
No. VLOOKUP takes a single lookup value. Combine the criteria into a helper key, or use a function such as XLOOKUP or INDEX with MATCH to test multiple conditions.
Why does my VLOOKUP with two criteria return #N/A?
The combined lookup value may not exactly equal a key in the helper column. Check the separator, spaces, spelling, data types, and whether the lookup range includes the matching row.
Does the helper column have to be the first column?
It must be the leftmost column in the table array supplied to VLOOKUP. It does not have to be the first column on the worksheet, but VLOOKUP cannot return a value to the left of its lookup column.
Can I use two criteria without adding a column?
Yes. In Microsoft 365 or Excel 2021, XLOOKUP can test both criteria in one formula. INDEX and MATCH can also do this, with array-entry requirements in some older Excel versions.
What if two rows have the same two criteria?
VLOOKUP returns the first matching row. If that is not the intended record, make the key more specific by adding another field or use a method designed to return multiple matches.
Related
- Excel Data Validation Lists: Create and Manage Dropdowns
- Excel Shuffle List of Names: 10 Easy Methods
- Excel: Find Where a Cell Is Used Across Sheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Concatenate in Excel: Can You Keep Cell Formatting?No—Excel’s CONCAT, CONCATENATE, TEXTJOIN, and the & operator join cell contents, but they do not carry the source…
- Sort Excel by Month: Dates, Names, and Custom Lists EasilyTo sort Excel by month, select the full data range and choose Data > Sort. Sort real date…
- Excel Top N Formula: Find the Highest Values and RowsTo return the top N numbers in Excel for Microsoft 365, use =LARGE(B2:B20,SEQUENCE(3)) to spill the three largest…
- Find the Highest Value in Excel with the MAX FunctionTo find the highest number in an Excel range, enter =MAX(B2:B20) in an empty cell, replacing B2:B20 with…
- NPV Analysis in Excel: Formula, Steps, and ExampleTo calculate net present value in Excel, enter the discount rate in one cell, put the initial investment…
- How to Copy and Concatenate Text in Excel with FormulasTo combine text from two cells, put the values in A2 and B2, then enter =A2&" "&B2 in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.