How to Perform a Two-Condition Lookup in Excel
Question details
The user needs to retrieve a specific value from a dataset by matching two different criteria (such as an ID and an item name) simultaneously.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for a specific data point in a table where a single lookup value is not unique enough, requiring multiple criteria to locate the exact match.
- Observed behavior
- Traditional VLOOKUP natively supports only a single lookup value, which means retrieving data based on two conditions requires array formulas, combined keys, or advanced functions like XLOOKUP.
Ensure that the data ranges you select for your criteria are exactly the same size to prevent formula calculation errors, and verify whether your version of Excel supports dynamic arrays or requires legacy array shortcuts.
Use XLOOKUP for Multiple Criteria (Microsoft 365 and Excel 2021)
XLOOKUP provides the cleanest and most efficient way to perform a two-condition lookup using boolean logic without needing legacy array shortcuts.
In modern versions of Excel, XLOOKUP handles arrays natively. By multiplying two criteria arrays together, you create an array of 1s (TRUE) and 0s (FALSE). The formula simply looks for the value 1.
Click on the empty cell where you want the matched result to appear.
Type =XLOOKUP(1, (CriteriaRange1=Criteria1) * (CriteriaRange2=Criteria2), ReturnRange). For example: =XLOOKUP(1, ($F$3:$F$10=$B3)*($G$3:$G$10=$A3), H$3:H$10, "").
Press Enter to execute the formula. Excel will evaluate both conditions and return the matched value from the return range.

Use VLOOKUP with the CHOOSE Function
If you are using an older version of Excel, you can use an array formula to merge two criteria into a single lookup key inside a VLOOKUP.
Use INDEX and MATCH for Two Conditions
The INDEX and MATCH combination is a powerful, flexible alternative to VLOOKUP for handling two-way lookups in any Excel version.
Easily Perform Complex Lookups with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, XLOOKUP, VLOOKUP, and INDEX/MATCH combinations. You can handle multiple-condition lookups effortlessly with top-tier Microsoft Excel compatibility.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the dataset you need to search.
- 2. Enter the lookup formula: Click the target cell and input your preferred formula, such as =XLOOKUP(1, (Range1=Condition1)*(Range2=Condition2), ResultRange).
- 3. Calculate the result: Press Enter (or Ctrl+Shift+Enter for legacy arrays) to instantly retrieve your accurately matched data.

Frequently Asked Questions
Why does my array formula return a #VALUE! error?
This usually happens in older versions of Excel if you press just Enter instead of Ctrl+Shift+Enter. Ensure you use the correct keyboard shortcut so the application wraps the formula in curly braces {}, evaluating it correctly as an array.
Can I use a helper column instead of writing an array formula?
Yes. If you prefer to avoid complex array formulas, you can insert a new column into your dataset that concatenates the two criteria (e.g., =A2&B2). You can then use a standard VLOOKUP to match a joined lookup value against this new helper column.
Is the XLOOKUP function available in all versions of Excel?
No, XLOOKUP is only available in Microsoft 365, Excel 2021, and Excel for the web. If you are using Excel 2019 or an older version, you must use the VLOOKUP or INDEX/MATCH methods.
How do I add more than two conditions in these formulas?
You can continuously chain conditions. For XLOOKUP or INDEX/MATCH, simply multiply another criteria array inside the formula: (Range1=Condition1) * (Range2=Condition2) * (Range3=Condition3).




