Still counting columns for VLOOKUP? There’s a better way. XLOOKUP fixes the annoying parts of its predecessor and adds features that actually make sense.
What Makes XLOOKUP Different
VLOOKUP forces you to organize data in a specific way. XLOOKUP doesn’t:
- Look left or right – Your columns can be in any order
- No column counting – Point directly at what you need
- Built-in error handling – Show custom messages instead of #N/A
- Multiple search modes – Exact matches, approximate matches, or wildcards
- Search direction control – Start from top or bottom
The Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])You only need the first three parts. The rest are optional.
Example 1: Basic Product Lookup
You have a product list and need to find a price:
| Product Code | Product Name | Price |
|---|---|---|
| P001 | Wireless Mouse | $29.99 |
| P002 | USB Keyboard | $45.50 |
| P003 | Monitor Stand | $67.25 |
Formula: =XLOOKUP("P002", A2:A4, C2:C4)
Result: $45.50
Find “P002” in column A, return the matching value from column C. No column numbers needed.
Example 2: Looking Left
Your price list has prices first, then product codes:
| Price | Product Name | Product Code |
|---|---|---|
| $29.99 | Wireless Mouse | P001 |
| $45.50 | USB Keyboard | P002 |
| $67.25 | Monitor Stand | P003 |
Formula: =XLOOKUP("P002", C2:C4, A2:A4)
Result: $45.50
VLOOKUP can’t do this. XLOOKUP doesn’t care where your columns are.
Example 3: Custom Error Messages
What if the product doesn’t exist?
Formula: =XLOOKUP("P999", A2:A4, C2:C4, "Product not found")
Result: “Product not found”
The fourth argument lets you show whatever message you want instead of an error.
Example 4: Tiered Lookups
Sales commission structure:
| Sales Threshold | Commission Rate |
|---|---|
| $0 | 5% |
| $10,000 | 7% |
| $25,000 | 10% |
| $50,000 | 12% |
Find the commission rate for $32,000 in sales:
Formula: =XLOOKUP(32000, A2:A5, B2:B5, , -1)
Result: 10%
The 1 tells XLOOKUP to find an exact match or the next smallest value. Great for pricing tiers, tax brackets, or any stepped structure. Leave the fourth argument empty by using two commas.
Example 5: Partial Text Matching
Customer list:
| Customer Name | Account ID |
|---|---|
| Smith Corp | ACC-1001 |
| Johnson Industries | ACC-1002 |
| Williams Group | ACC-1003 |
Formula: =XLOOKUP("*Johnson*", A2:A4, B2:B4, "Not found", 2)
Result: ACC-1002
The 2 in match_mode turns on wildcard matching. Use * for multiple characters or ? for single characters.
Example 6: Search Backwards
Transaction log with duplicate IDs:
| Date | Transaction ID | Amount |
|---|---|---|
| 1/15 | TXN-5001 | $250 |
| 1/22 | TXN-5001 | $175 |
| 1/29 | TXN-5001 | $300 |
Formula: =XLOOKUP("TXN-5001", B2:B4, C2:C4, , 0, -1)
Result: $300
The -1 in search_mode tells XLOOKUP to start from the bottom and work up. You get the most recent transaction.
Match Modes (Fifth Argument)
- 0 (default): Exact match only
- 1: Exact match or next smallest (for sorted data)
- -1: Exact match or next largest (for sorted data)
- 2: Wildcard match
Search Modes (Sixth Argument)
- 1 (default): Search first to last
- -1: Search last to first
Start Using It
Open a blank spreadsheet. Set up a simple table. Try each example above. You’ll get comfortable with XLOOKUP faster than you think.
Once you start using it, you’ll forget about VLOOKUP.
Questions about XLOOKUP? Want to see it applied to your specific work? Let’s connect.