Function Problems

How to Perform a Two-Condition Lookup in Excel

Bushra ParveenBushra Parveen Nov 11, 2026 869 views

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.

How to Perform a Two-Condition Lookup in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on the empty cell where you want the matched result to appear.

2
Enter the XLOOKUP formula

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, "").

3
Execute the function

Press Enter to execute the formula. Excel will evaluate both conditions and return the matched value from the return range.

Use XLOOKUP for Multiple Criteria (Microsoft 365 and Excel 2021)
Handling Missing Matches: The last argument in XLOOKUP allows you to specify a custom message like "Not Found" instead of returning an #N/A error if the two conditions do not match any row.
Advanced Data Analysis

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. 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the dataset you need to search.
  2. 2. Enter the lookup formula: Click the target cell and input your preferred formula, such as =XLOOKUP(1, (Range1=Condition1)*(Range2=Condition2), ResultRange).
  3. 3. Calculate the result: Press Enter (or Ctrl+Shift+Enter for legacy arrays) to instantly retrieve your accurately matched data.
100% compatible with Microsoft Excel formulas and the .xlsx format.Supports modern dynamic array functions including XLOOKUP for multi-criteria searches.Free to download and features a lightweight, intuitive tabbed interface for high productivity.
microsoft office alternative - wps office

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).