How to Use XLOOKUP with Multiple Criteria in Excel

Excel’s XLOOKUP function is a powerful replacement for VLOOKUP and INDEX-MATCH, but most guides only show you how to look up values based on a single column. In real-world spreadsheets, you often need to find a value based on two or more conditions at the same time. For example, finding a specific employee’s sales figure for a specific quarter, or retrieving a product price based on both the product name and the region.

This guide explains how to combine XLOOKUP with simple concatenation techniques to perform lookups based on multiple criteria — without needing complex array formulas or helper columns.

Why Standard XLOOKUP Only Handles One Criterion

The basic XLOOKUP function searches for a single value in a single column or row. Its syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array)

This works perfectly when your lookup column contains unique identifiers. However, if you have a spreadsheet where the same employee name appears multiple times (once for each quarter), a basic XLOOKUP will only return the first match it finds. It has no way to distinguish between “John – Q1” and “John – Q3” because it only looks at one column.

The Concatenation Method: Combining Criteria into One Lookup

The most practical way to use XLOOKUP with multiple criteria is to concatenate (join together) your lookup values and your lookup arrays using the ampersand (&) operator. This creates a combined key that XLOOKUP can search against.

How the Formula Works

Suppose you have a dataset with three columns:

• Column A: Employee Name
• Column B: Quarter (Q1, Q2, Q3, Q4)
• Column C: Sales Figure

You want to find the sales figure for a specific employee in a specific quarter. The formula is:

=XLOOKUP(G2&H2, A2:A100&B2:B100, C2:C100)

Here is what each part does:

• G2&H2 — Combines the employee name (in cell G2) and the quarter (in cell H2) into a single lookup string. If G2 contains “Sarah” and H2 contains “Q3”, this creates “SarahQ3”.
• A2:A100&B2:B100 — Combines every employee name with its corresponding quarter in the data range. Row 2 might become “SarahQ1”, row 3 might become “JohnQ2”, and so on.
• C2:C100 — The column from which the matching sales figure is returned.

XLOOKUP then searches the combined array for “SarahQ3” and returns the corresponding value from Column C.

Adding a Delimiter for Safety

When concatenating values, there is a subtle risk. If one employee is named “Jan” and the quarter is “Q1”, the combined string is “JanQ1”. But if another employee is named “JanQ” and the quarter is “1”, the combined string is also “JanQ1”. Although this edge case is rare, you can avoid it entirely by adding a delimiter character between the values:

=XLOOKUP(G2&"|"&H2, A2:A100&"|"&B2:B100, C2:C100)

This creates “Jan|Q1” and “JanQ|1” — two distinct strings that will never accidentally match.

Using Three or More Criteria

The concatenation method scales easily. If you need to look up a value based on three columns — for example, Employee, Quarter, and Region — simply add another ampersand and another column:

=XLOOKUP(G2&"|"&H2&"|"&I2, A2:A100&"|"&B2:B100&"|"&C2:C100, D2:D100)

Each additional criterion is joined with the same delimiter. There is no practical limit to the number of criteria you can combine this way.

Handling Errors When No Match Is Found

If XLOOKUP cannot find a match, it returns a #N/A error by default. You can handle this gracefully using the optional fourth argument:

=XLOOKUP(G2&"|"&H2, A2:A100&"|"&B2:B100, C2:C100, "Not Found")

This returns the text “Not Found” instead of an error, making your spreadsheet easier to read and preventing error cascading in downstream formulas.

When to Use a Helper Column Instead

The concatenation-inside-XLOOKUP method is clean and efficient for most situations. However, if your spreadsheet contains tens of thousands of rows and you are using multiple XLOOKUP formulas simultaneously, performance may become sluggish because Excel recalculates the concatenated array every time the spreadsheet updates.

In this case, consider adding a dedicated helper column that pre-computes the concatenated key:

1. Insert a new column (for example, Column D).
2. In cell D2, enter: =A2&"|"&B2
3. Copy this formula down for all rows.
4. Use a standard XLOOKUP against the helper column: =XLOOKUP(G2&"|"&H2, D2:D100, C2:C100)

Because the helper column values are static text (after calculation), the XLOOKUP only needs to compare strings rather than recalculate them, which is significantly faster on large datasets.

Common Mistakes to Avoid

• Mismatched data types: If one column contains numbers stored as text and the other contains actual numbers, the concatenation will produce different strings. Use the TEXT() function to ensure consistent formatting: =XLOOKUP(G2&"|"&TEXT(H2,"0"), A2:A100&"|"&TEXT(B2:B100,"0"), C2:C100)
• Extra spaces: Leading or trailing spaces in cell values will cause mismatches. Use TRIM() to clean the data: =XLOOKUP(TRIM(G2)&"|"&TRIM(H2), TRIM(A2:A100)&"|"&TRIM(B2:B100), C2:C100)
• Case sensitivity: XLOOKUP is not case-sensitive by default, so “Sarah” and “sarah” will match. This is usually desirable, but if you need case-sensitive matching, you will need to use the EXACT function inside a different formula approach.
• Array range mismatch: All array arguments in the XLOOKUP formula must be the same size. If your lookup array covers rows 2 to 100, your return array must also cover exactly the same number of rows.

XLOOKUP Multiple Criteria vs INDEX-MATCH

Before XLOOKUP existed, the standard approach for multi-criteria lookups was an array-entered INDEX-MATCH formula:

=INDEX(C2:C100, MATCH(1, (A2:A100=G2)*(B2:B100=H2), 0))

This formula required pressing Ctrl + Shift + Enter to confirm it as an array formula (in older Excel versions). XLOOKUP with concatenation achieves the same result without needing array entry, making it simpler to write, easier to read, and less error-prone.

If you are already comfortable with INDEX-MATCH, both approaches are valid. However, for new formulas, XLOOKUP is generally the better choice because of its cleaner syntax and built-in error handling.

Practical Use Cases

• HR spreadsheets: Look up an employee’s bonus based on both their department and their performance rating.
• Sales reports: Find revenue for a specific product in a specific month.
• Inventory management: Retrieve stock levels for a specific item at a specific warehouse location.
• Financial modelling: Pull account balances based on account code and fiscal year.
• Student records: Find a student’s grade for a specific subject in a specific term.

The multi-criteria XLOOKUP technique is one of the most practical advanced Excel skills you can learn. Once you understand the concatenation principle, you can apply it to virtually any lookup scenario where a single criterion is not enough.