Master XLOOKUP With Multiple Criteria Exact Match Now

To perform an XLOOKUP with multiple criteria exact match, skip brittle helper columns and use boolean array logic. Multiply your criteria ranges together to generate an array of 1s and 0s, and lookup the value 1.

Excel

=XLOOKUP(1, (A2:A100=F2) * (B2:B100=G2), C2:C100)

The Syntax Breakdown

• 1: The lookup_value. We are explicitly telling Excel to search for a 1 (which represents TRUE) within our generated boolean array.
• (A2:A100=F2): Condition 1. This checks if the values in column A exactly match your first criterion in cell F2. In Excel memory, this creates an array of TRUE and FALSE values.
• *: The mathematical multiplication operator. When you multiply TRUE/FALSE arrays in Excel, they convert into 1s and 0s. It acts as an AND operator (e.g., TRUE * TRUE = 1, TRUE * FALSE = 0).
• (B2:B100=G2): Condition 2. This checks if the values in column B exactly match your second criterion in cell G2.
• C2:C100: The return_array. This is the column containing the exact value you want to retrieve once the formula finds the single row where both conditions evaluated to 1.

Real-World Example: Retrieving Regional Employee Salary Bands

You are an HR Operations Manager standardizing compensation. You need to pull the exact Salary Band for specific job titles, but title names are shared across different geographical regions. A “Marketing Manager” in New York has a different band than a “Marketing Manager” in London.

You must match both Region (Condition 1) and Role (Condition 2) to get the correct Salary Band.

Here is your raw data table:

The Application:

You have a reporting dashboard where cell F2 contains the target Region (New York) and G2 contains the target Role (Marketing Manager).

Apply the boolean XLOOKUP formula in your dashboard:

Excel

=XLOOKUP(1, (A2:A5=F2) * (B2:B5=G2), C2:C5)

The Output:

The formula successfully bypasses Row 3 (London) and returns Band 6 from Row 4, because Row 4 is the only row where the boolean array evaluates to 1 * 1 = 1.

Common Errors & How to Fix Them

• #VALUE! Error: Your array ranges do not match in size, which breaks the array multiplication. Fix: Ensure your criteria ranges and return range have the exact same number of rows (e.g., do not mix A2:A100 with B2:B90).
• #N/A Error: The formula executed correctly, but the exact combination of criteria does not exist in your dataset. Fix: Utilize XLOOKUP‘s native fourth argument to handle missing data cleanly: =XLOOKUP(1, (A2:A100=F2)*(B2:B100=G2), C2:C100, "Not Found").
• Formula returns #N/A despite a visible match: One of your criteria involves a number stored as text, while the raw data is formatted as a number (or vice versa). Fix: Standardize your data types using Data > Text to Columns, or wrap your cell reference in the VALUE() function if converting text to numbers dynamically.