XLOOKUP with Multiple Criteria: Advanced Lookup Formula Guide

Last quarter, our sales analyst spent hours manually cross-referencing customer orders across multiple spreadsheets. She needed to find order details by matching both customer ID AND order date—something VLOOKUP couldn't handle.

After I showed her how to use XLOOKUP with multiple criteria, what took 2 hours of manual work now takes 2 seconds with a formula.

This comprehensive guide covers everything you need to know about using XLOOKUP with multiple search criteria to solve complex lookup problems that would make VLOOKUP cry.

Why XLOOKUP with Multiple Criteria Matters

Single-criteria lookups are limiting:

• What if you need to find price for Product A in Region B?
• How do you look up employee data by both Department AND Job Title?
• Can you find sales by Customer AND Month?

Traditional solutions are clumsy:

• VLOOKUP + INDEX/MATCH: Complex, error-prone
• Helper columns: Extra work, clutters spreadsheet
• Array formulas: Difficult to understand and maintain

XLOOKUP with multiple criteria solves this elegantly—no helper columns, clean formulas, easy to understand.

Prerequisites: XLOOKUP Basics

XLOOKUP syntax:

Simple example:

Looks up value in E2, searches column A, returns matching value from column C

XLOOKUP availability: Excel 365, Excel 2021, Excel for web (not in older versions)

Method 1: Concatenate Multiple Criteria

The most straightforward approach: combine your criteria into a single lookup value.

Example Scenario: Product Pricing by Region

Data Structure:

Goal: Find price for "Widget A" in "South" region

Formula:

1=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100)

Where:

• E2 = Product name (e.g., "Widget A")
• F2 = Region (e.g., "South")
• A2:A100&B2:B100 = Concatenated lookup array
• C2:C100 = Price column to return

How it works:

1. E2&F2 creates "Widget ASouth"
2. A2:A100&B2:B100 creates array like "Widget ASouth", "Widget ANorth", etc.
3. XLOOKUP finds the match and returns corresponding price

Adding a Delimiter for Clarity

Problem: "Widget A" + "South" = "Widget ASouth" (hard to debug)

Solution: Add a separator:

1=XLOOKUP(E2&"|"&F2, A2:A100&"|"&B2:B100, C2:C100)

Now it searches for "Widget A|South" making debugging easier.

Real-World Example: Sales Lookup

Scenario: Find sales figures by Customer Name AND Month

Data:

Lookup Formula:

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

• G2 = Customer name (e.g., "Acme Corp")
• H2 = Month (e.g., "Feb")
• Returns: $52,000

With error handling:

1=XLOOKUP(G2&"-"&H2, A2:A100&"-"&B2:B100, C2:C100, "No sales data for this customer/month combination")

Method 2: Using XLOOKUP with Three or More Criteria

Three-Criteria Lookup

Example: Find inventory count by Product + Location + Size

Data:

Formula:

1=XLOOKUP(E2&F2&G2, A2:A100&B2:B100&C2:C100, D2:D100)

Where:

• E2 = Product (e.g., "T-Shirt")
• F2 = Location (e.g., "Warehouse A")
• G2 = Size (e.g., "Small")
• Returns: 150

With delimiters (recommended):

1=XLOOKUP(E2&"|"&F2&"|"&G2, A2:A100&"|"&B2:B100&"|"&C2:C100, D2:D100, "Item not found in inventory")

Four or More Criteria

Simply extend the concatenation:

1=XLOOKUP(E2&F2&G2&H2, A2:A100&B2:B100&C2:C100&D2:D100, E2:E100)

Performance Note: More criteria = slower calculation. For 5+ criteria, consider database solutions or Power Query.

Method 3: Case-Sensitive Multiple Criteria Lookup

By default, XLOOKUP ignores case. If you need case-sensitive matching:

Using EXACT Function

1=XLOOKUP(TRUE, 
2    (EXACT(A2:A100,E2))*(EXACT(B2:B100,F2)), 
3    C2:C100, 
4    "No match")

How it works:

• EXACT(A2:A100,E2) returns TRUE/FALSE for case-sensitive match in column A
• EXACT(B2:B100,F2) does the same for column B
• Multiplication creates an AND logic (both must match)
• XLOOKUP finds the first TRUE

Example Use Case:

• Product codes where "abc" and "ABC" are different products
• Passwords or security tokens
• Part numbers with case significance

Method 4: Approximate Match with Multiple Criteria

Scenario: Find commission rate based on Sales Amount AND Employee Tier

Commission Table:

Formula (exact tier, approximate sales match):

1=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100, , 1)

Where:

• E2 = Tier (e.g., "Silver")
• F2 = Sales amount (e.g., 75000)
• 1 = Exact match on first, next smaller item on second

Note: This requires data sorted by the approximate match column (Sales Threshold).

Method 5: Returning Multiple Columns

Goal: Look up by multiple criteria, return several columns

Example: Find employee details by Department + Job Title

Data:

Formula to return Name, Email, AND Salary:

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

Where:

• G2 = Department (e.g., "Sales")
• H2 = Job Title (e.g., "Manager")
• C2:E100 = Return entire range (Name, Email, Salary columns)

Result: Single formula returns all three values:

• John Doe | j.doe@co.com | 85000

To display in separate cells:

In cell I2:

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

In cell J2:

1=INDEX(XLOOKUP(G2&"|"&H2, A2:A100&"|"&B2:B100, C2:E100), 2)

In cell K2:

1=INDEX(XLOOKUP(G2&"|"&H2, A2:A100&"|"&B2:B100, C2:E100), 3)

Advanced Techniques

Technique 1: Dynamic Multiple Criteria

Scenario: Sometimes you want to search by 2 criteria, sometimes 3

Solution using IF logic:

1=IF(G2="",
2    XLOOKUP(E2&F2, A2:A100&B2:B100, D2:D100),
3    XLOOKUP(E2&F2&G2, A2:A100&B2:B100&C2:C100, D2:D100)
4)

If G2 is empty, use 2-criteria lookup; otherwise use 3-criteria lookup.

Technique 2: Wildcard with Multiple Criteria

Scenario: Lookup by exact Product and partial Customer Name

Formula:

1=XLOOKUP(TRUE, 
2    (A2:A100=E2)*(ISNUMBER(SEARCH(F2, B2:B100))), 
3    C2:C100, 
4    "Not found")

Where:

• A2:A100=E2 = Exact match for Product
• SEARCH(F2, B2:B100) = Partial match for Customer Name
• Finds first row where both conditions are TRUE

Technique 3: Multiple Criteria with Dates

Scenario: Find sales by Customer AND date range

Formula (Customer exact, date between start and end):

1=XLOOKUP(TRUE,
2    (A2:A100=E2)*
3    (B2:B100>=F2)*
4    (B2:B100<=G2),
5    C2:C100,
6    "No sales in this period")

Where:

• E2 = Customer name
• F2 = Start date
• G2 = End date
• Finds sales where customer matches AND date is within range

Technique 4: Multiple Criteria with OR Logic

Default: Multiple criteria use AND (all must match)

For OR logic (any can match):

1=XLOOKUP(TRUE,
2    (A2:A100=E2)+(B2:B100=F2),
3    C2:C100,
4    "No match")

The + operator creates OR logic:

• Match if Product = E2 OR Region = F2

Common Errors and Solutions

#N/A Error

Problem: No matching values found

Solutions:

1. Add error handling:

1=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100, "Not found in database")

1. Check for extra spaces:

1=XLOOKUP(TRIM(E2)&TRIM(F2), TRIM(A2:A100)&TRIM(B2:B100), C2:C100)

1. Verify data types match (text vs. numbers)

#VALUE! Error

Problem: Array dimensions don't match

Solution: Ensure lookup arrays are same size:

❌ Wrong:

1=XLOOKUP(E2&F2, A2:A100&B2:B150, C2:C100)  // B has more rows

✅ Correct:

1=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100)  // All same size

#SPILL! Error

Problem: Formula wants to return array but cells below/right are occupied

Solution: Clear cells where results will spill, OR use single value extraction with INDEX

Performance Issues (Slow Calculation)

Problem: Formula recalculates slowly on large datasets

Solutions:

1. Reduce lookup range to necessary rows only
2. Use Excel Tables (automatically adjusts ranges)
3. Consider Power Query for very large datasets (100K+ rows)
4. Use structured references with Tables for better performance

Practical Examples by Industry

Retail: Product Pricing Lookup

Find price by Product Code + Customer Type + Quantity Tier:

1=XLOOKUP(
2    ProductCode&CustomerType&QuantityTier,
3    Products&Customers&QtyRanges,
4    Prices,
5    "Contact sales for quote"
6)

HR: Employee Information Retrieval

Find employee data by Department + Position + Location:

1=XLOOKUP(
2    Dept&Position&Office,
3    EmployeeDepts&EmployeePositions&EmployeeOffices,
4    EmployeeData,
5    "Employee not found"
6)

Finance: Budget Allocation

Find budget by Cost Center + Project + Quarter:

1=XLOOKUP(
2    CostCenter&Project&Quarter,
3    CostCenters&Projects&Quarters,
4    BudgetAmounts,
5    0
6)

Logistics: Shipping Rates

Find shipping rate by Origin + Destination + Weight Class:

1=XLOOKUP(
2    Origin&Destination&WeightClass,
3    Origins&Destinations&Weights,
4    ShippingRates,
5    "Rate not available"
6)

XLOOKUP vs. Alternatives

XLOOKUP vs. INDEX/MATCH

Old method (INDEX/MATCH with multiple criteria):

1=INDEX(C2:C100, 
2    MATCH(E2&F2, A2:A100&B2:B100, 0))

XLOOKUP equivalent:

1=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100)

Winner: XLOOKUP (simpler, more readable)

XLOOKUP vs. SUMIFS/COUNTIFS

For aggregating (sum/count) with criteria, use SUMIFS/COUNTIFS:

1=SUMIFS(Sales, Products, E2, Regions, F2)

For lookup (finding a single value), use XLOOKUP.

XLOOKUP vs. FILTER

FILTER returns all matching rows (multiple results):

1=FILTER(A2:C100, (A2:A100=E2)*(B2:B100=F2))

XLOOKUP returns first matching row only.

Use FILTER when: You want all matches Use XLOOKUP when: You want the first (or last) match

Best Practices

1. Use Delimiters

✅ Good:

1=XLOOKUP(E2&"|"&F2, A2:A100&"|"&B2:B100, C2:C100)

❌ Bad:

1=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100)

Why: Delimiter prevents false matches (e.g., "12" & "34" vs "1" & "234" both become "1234" without delimiter)

2. Add Error Handling

Always include the 4th argument (if_not_found):

1=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100, "No match found")

3. Use Excel Tables

Convert ranges to Tables (Ctrl+T):

1=XLOOKUP(
2    Criteria1&Criteria2,
3    Table1[Column1]&Table1[Column2],
4    Table1[ResultColumn],
5    "Not found"
6)

Benefits:

• Formulas auto-update when data added
• More readable
• Better performance

4. Document Complex Formulas

Add a comment cell explaining:

• What criteria are being matched
• What result is returned
• Any special logic or edge cases

5. Test Edge Cases

Test your formula with:

• No match found
• Multiple matches (returns first)
• Empty criteria cells
• Special characters in criteria

Frequently Asked Questions

Can XLOOKUP search from bottom to top?

Yes! Use search_mode argument = -1:

1=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100, , , -1)

This returns the LAST match instead of first.

How many criteria can I use?

Technically unlimited, but:

• More criteria = slower calculation
• More than 5-6 criteria may indicate you need a database
• Consider Power Query or proper database for complex scenarios

Does XLOOKUP work with different data types?

Yes, but concatenation converts everything to text:

• Numbers become text: 123 becomes "123"
• Dates become serial numbers: 1/1/2024 becomes "45292"
• For dates, use TEXT function: TEXT(DateColumn, "mm/dd/yyyy")

Can I use XLOOKUP with criteria on different sheets?

Yes:

1=XLOOKUP(
2    E2&F2,
3    Sheet2!A:A&Sheet2!B:B,
4    Sheet2!C:C
5)

What if my lookup columns aren't adjacent?

No problem! XLOOKUP doesn't care about column order:

1=XLOOKUP(E2&F2, A2:A100&D2:D100, Z2:Z100)

Lookups in columns A and D, returns from column Z.

Related articles: Master XLOOKUP in Excel, Excel INDEX MATCH: Dynamic Duo