Advanced XLOOKUP: Multi-Criteria (2025)
Quick Answer: Multi-criteria: =XLOOKUP(A2&B2, col1&col2, return). Combine lookup values with &. Two-way: =XLOOKUP(row, rows, XLOOKUP(col, cols, data)) nested for row+column. Return multiple: =XLOOKUP(value, lookup, B:D) spills columns. Wildcard: =XLOOKUP(A2&'*', data, return, , 2) partial match.
Advanced XLOOKUP Techniques
This guide covers advanced XLOOKUP patterns: multi-criteria lookups, nested two-way lookups, returning multiple columns, wildcard searches, and complex conditional logic. Requires Excel 365 with XLOOKUP function.
⚠️ Prerequisites
- • Excel 365 required: XLOOKUP not in Excel 2019 or earlier
- • Know basic XLOOKUP: See our XLOOKUP basics guide first
- • Understand arrays: Results may spill to multiple cells
Advanced Techniques Covered:
1. Multi-Criteria Lookups
Combine multiple columns to find exact match
=XLOOKUP(A2&B2, col1&col2, return)
2. Two-Way Lookups (Nested)
Find row AND column intersection
=XLOOKUP(row, rows, XLOOKUP(col, cols, data))
3. Return Multiple Columns
Spill entire record with multiple fields
=XLOOKUP(value, lookup, B:E)
4. Wildcard Searches
Partial text matching with wildcards
=XLOOKUP(A2&"*", data, return, , 2)
Why Advanced XLOOKUP?
✓ Advantages:
- • Replaces INDEX MATCH arrays
- • Cleaner than SUMIFS lookups
- • No helper columns needed
- • Handles complex criteria easily
- • Returns multiple columns at once
- • Bidirectional search capability
✗ Considerations:
- • Excel 365 only
- • Can be slower than INDEX MATCH
- • Complex formulas harder to debug
- • Spilling may need space
- • Not compatible with older Excel
Real-World Applications:
Pricing tables: Find price by Product+Region+Tier
HR systems: Look up employee by Name+Department
Inventory: Find stock by SKU+Location+Date
Cross-tabulations: Two-way lookups for matrix data
Complete records: Return all customer fields at once
💡 Performance Tip
Multi-criteria with &: Concatenating lookup values (A&B) is fast and clean. Alternative: nested IF statements are slower and harder to maintain. For 3+ criteria, consider helper columns for better readability and debugging.
Advanced XLOOKUP Patterns
1. Multi-Criteria Lookup
=XLOOKUP(lookup1&lookup2, array1&array2, return_array)
How it works: Concatenates lookup values and arrays with & operator. Finds exact combined match.
Example: Find by Name + Department
=XLOOKUP(E2&F2, A:A&B:B, C:C)
E2="John", F2="Sales" → finds John in Sales dept
Tips:
- • Use same separator in both lookup and array
- • Add delimiter for clarity: A2&"-"&B2
- • Works with 3+ criteria: A2&B2&C2
2. Two-Way Lookup (Nested)
=XLOOKUP(row_value, row_array, XLOOKUP(col_value, col_array, data_range))
How it works: Inner XLOOKUP finds column, outer XLOOKUP finds row in that column. Matrix intersection.
Example: Price by Product + Month
=XLOOKUP(D2, A:A, XLOOKUP(E2, B1:M1, B:M))
D2=Product, E2=Month → finds price at intersection
Order matters:
- • Inner: column lookup (horizontal headers)
- • Outer: row lookup (vertical headers)
- • Data range must cover entire matrix
3. Return Multiple Columns
=XLOOKUP(lookup_value, lookup_array, B:E)
How it works: return_array spans multiple columns. Results spill automatically to adjacent cells.
Example: Return Name, Email, Phone, Dept
=XLOOKUP(A2, CustomerID, B:E)
Returns 4 columns of customer data
Spilling:
- • Results fill cells to the right automatically
- • Ensure space is empty or #SPILL! error
- • Can return entire table: B:Z
4. Wildcard Searches
=XLOOKUP(lookup_value&"*", lookup_array, return_array, , 2)
How it works: Match mode 2 enables wildcards. * = any characters, ? = single character.
Example: Find names starting with "John"
=XLOOKUP(E2&"*", A:A, B:B, , 2)
E2="John" → matches John, Johnny, Johnson
Wildcards:
- • * = zero or more characters
- • ? = exactly one character
- • Mode 2 required for wildcards
⚠️ Important Notes
- • Array sizes must match: Lookup and array same height/width
- • Concatenation order: A&B different from B&A - be consistent
- • Performance: Multi-criteria slightly slower than single
- • Debugging: Test inner formulas separately before nesting
- • Spilling: Ensure cells to right are empty for multi-column returns
- • Case sensitivity: XLOOKUP is case-insensitive by default
10+ Advanced XLOOKUP Examples
Multi-Criteria Lookups
1. Two-Column Match
=XLOOKUP(E2&F2, A:A&B:B, C:C)
Find by combining Name + Department
2. Three-Criteria Match
=XLOOKUP(E2&F2&G2, A:A&B:B&C:C, D:D)
Product + Region + Year lookup
3. With Delimiter
=XLOOKUP(E2&"-"&F2, A:A&"-"&B:B, C:C)
Clearer separation with hyphen
Two-Way Lookups
4. Matrix Lookup (Row + Column)
=XLOOKUP(D2, A:A, XLOOKUP(E2, B1:M1, B:M))
Find intersection in pricing matrix
5. Cross-Tab Report
=XLOOKUP($A2, Data[Product], XLOOKUP(B$1, Data[Month], Data[Sales]))
Dynamic cross-tabulation with table references
Multiple Column Returns
6. Complete Record
=XLOOKUP(A2, CustomerID, B:E)
Return Name, Email, Phone, Address (4 columns)
7. Entire Row
=XLOOKUP(A2, Data[ID], Data[[Name]:[Status]])
All fields from Excel Table
Wildcard & Partial Matching
8. Starts With
=XLOOKUP(E2&"*", A:A, B:B, , 2)
Match beginning of text
9. Contains
=XLOOKUP("*"&E2&"*", A:A, B:B, , 2)Find text anywhere in string
10. Single Character Wildcard
=XLOOKUP("A?C", A:A, B:B, , 2)Match A_C where _ is any single character
Complex Scenarios
11. Multi-Criteria with Default
=XLOOKUP(E2&F2, A:A&B:B, C:C, "Not Found")
Custom message if no match
12. Approximate Multi-Criteria
=XLOOKUP(E2&F2, A:A&B:B, C:C, , 1)
Next smaller match for ranges
🎯 Pro Tips
- • Consistent separators: Use same delimiter in lookup and array
- • Test pieces first: Verify inner formulas before nesting
- • Named ranges: Make complex formulas more readable
- • Spill space: Clear cells to right for multi-column returns
- • Performance: Limit ranges when possible (A:A slower than A2:A1000)
- • Error handling: Always include if_not_found argument
- • Exact match default: Mode 0 is default, specify others explicitly
Frequently Asked Questions
Should I use multi-criteria XLOOKUP or helper columns?
Multi-criteria XLOOKUP (A&B) is cleaner and more maintainable - no extra columns. Helper columns are faster for very large datasets (100k+ rows) and easier to debug. Use multi-criteria for most cases, helper columns for performance-critical massive datasets or when formula complexity becomes unmanageable.
How does two-way XLOOKUP compare to INDEX MATCH?
Two-way XLOOKUP: =XLOOKUP(row, rows, XLOOKUP(col, cols, data)). INDEX MATCH: =INDEX(data, MATCH(row, rows, 0), MATCH(col, cols, 0)). XLOOKUP is more readable and handles errors better (if_not_found). INDEX MATCH slightly faster. Both work in Excel 365. Use XLOOKUP for new workbooks, INDEX MATCH for compatibility.
Can I use wildcards with multi-criteria lookups?
Yes, but tricky. Example: =XLOOKUP(A2&B2&'*', col1&col2, return, , 2). Wildcard applies to combined string. A2='John', B2='Sales' matches 'JohnSales123'. Better approach: wildcard on single column, exact on others, or use FILTER with multiple conditions for complex wildcard + exact combinations.
What's the performance impact of nested XLOOKUP?
Nested XLOOKUP recalculates for each cell - can be slow for large datasets. Inner XLOOKUP runs once per outer lookup. 1000 rows = 1000 inner calculations. Alternatives: (1) Helper column for inner result, (2) INDEX with MATCH MATCH for better performance, (3) FILTER for different approach. For < 1000 rows, nested XLOOKUP fine.
How do I debug complex multi-criteria XLOOKUP?
Break it down: (1) Test lookup value: =E2&F2 in separate cell, (2) Test array: =A2&B2 to verify format, (3) Check for extra spaces with TRIM, (4) Use if_not_found to catch errors, (5) Verify data types match (text vs numbers). Common issues: inconsistent separators, leading/trailing spaces, case sensitivity assumptions.
Can XLOOKUP return rows instead of columns?
Yes! XLOOKUP can return entire rows: =XLOOKUP(A2, lookup_col, B:Z) returns multiple columns. For returning multiple rows (vertical spill), use FILTER instead: =FILTER(A:C, A:A=value). XLOOKUP returns single match, FILTER returns all matches. Combine for powerful queries: =SORT(FILTER(...)).