This article shows you a basic XLOOKUP example in Excel: how to look up a value in one column and return a matching value from another column. You enter a lookup value (like a product ID), the range where to find it, and the range from which to return the result, and XLOOKUP gives you the match. It replaces older functions like VLOOKUP and HLOOKUP with a simpler, more flexible syntax.
What Is XLOOKUP?
XLOOKUP is a modern Excel function introduced in 2020 that searches a range or array for a match and returns a corresponding item from a second range or array. Its syntax is:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: the value you want to search for (e.g., a product code, name, or ID).
- lookup_array: the range where you search for that value (must be a single row or column).
- return_array: the range from which to return the corresponding value (same size as lookup_array).
- if_not_found (optional): text or value to show if no match is found. If omitted, Excel returns #N/A.
- match_mode (optional): 0 for exact match (default), -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcard match.
- search_mode (optional): 1 for search from first (default), -1 for last, 2 for binary ascending, -2 for binary descending.
The function works in Excel 365, Excel 2021, and later versions. It is not available in older versions like Excel 2016 or 2019.
Basic XLOOKUP Example (Step by Step)
Suppose you have a small table of products with their prices:
A B 1 Product Price 2 Apple 0.50 3 Banana 0.30 4 Cherry 0.75
You want to find the price of “Banana”. Here’s how:
- Click on an empty cell where you want the result, say D2.
- Type:
=XLOOKUP("Banana", A2:A4, B2:B4) - Press Enter. The cell shows 0.30.
That’s the entire basic usage. The function looks for “Banana” in cells A2:A4, finds it in row 3, and returns the value from B3 (0.30).
Why Use XLOOKUP Instead of VLOOKUP?
XLOOKUP solves several long-standing frustrations with VLOOKUP:
- No column index number: VLOOKUP requires a column number; XLOOKUP uses a return range, so you don’t count columns.
- Works to the left: VLOOKUP can only return values to the right of the lookup column. XLOOKUP can return from any range, even if it’s to the left.
- Exact match by default: VLOOKUP defaults to approximate match, which often causes errors. XLOOKUP defaults to exact match.
- Built-in error handling: You can specify a custom message when no match is found, instead of getting a cryptic #N/A.
- No need for table array sorting: VLOOKUP with approximate match requires sorted data; XLOOKUP does not.
For example, to return a value to the left, you could use =XLOOKUP(D2, B2:B4, A2:A4) to get the product name from a price.
Common Mistakes and How to Avoid Them
- Mismatched range sizes: The lookup_array and return_array must be the same size. If they differ, XLOOKUP returns #VALUE!.
- Using whole columns: While you can use A:A and B:B, it’s better to use specific ranges (like A2:A100) to avoid performance issues and accidental matches from header rows.
- Forgetting to lock ranges: When copying the formula down, you need absolute references (e.g., $A$2:$A$4) so the ranges don’t shift.
- Mixing data types: If your lookup value is a number stored as text, or vice versa, XLOOKUP may not match. Ensure both are the same type.
- Not handling not-found cases: Always provide the fourth argument to show a friendly message, like
=XLOOKUP(D2, A2:A4, B2:B4, "Not found").
Advanced Options: Match Modes and Wildcards
XLOOKUP supports wildcard characters when match_mode is 2. Use:
*(asterisk) for any sequence of characters.?(question mark) for any single character.~(tilde) to escape the wildcard (e.g., to find a literal asterisk).
Example: =XLOOKUP("Ban*", A2:A4, B2:B4, "Not found", 2) would match “Banana”.
The match_mode parameter also allows approximate matches: set it to -1 to get the exact match or the next smaller value, or 1 for the next larger. This is useful for tiered pricing or tax brackets, but requires the lookup_array to be sorted in ascending order for -1 and descending for 1.
XLOOKUP vs. INDEX/MATCH
INDEX/MATCH is an older combination that also offers flexibility, but XLOOKUP is simpler and more readable. For example, to get the price of “Banana” with INDEX/MATCH, you would write:
=INDEX(B2:B4, MATCH("Banana", A2:A4, 0))
That’s two functions nested. XLOOKUP does it in one. While INDEX/MATCH can handle more complex scenarios (like two-dimensional lookups), XLOOKUP covers most everyday needs with less chance of error.
Frequently Asked Questions
Is XLOOKUP available in Excel 2019?
No. XLOOKUP is only available in Excel 365 (as of 2020) and Excel 2021. If you have an older version, you can use VLOOKUP or INDEX/MATCH instead.
Why is my XLOOKUP returning #N/A?
#N/A means the lookup value was not found. Check for typos, extra spaces, or mismatched data types. Also ensure the lookup_array is correct and that you haven’t accidentally used the return_array as the lookup_array.
Can XLOOKUP return multiple values?
Yes, if you use a return_array that spans multiple columns, XLOOKUP will return all matching values in a spilled array. For example, =XLOOKUP(A2, A2:A4, B2:C4) returns two values (price and stock) that spill into adjacent cells.
How do I use XLOOKUP to find the last match?
Set the search_mode argument to -1. For example, =XLOOKUP("Banana", A2:A4, B2:B4, "Not found", 0, -1) finds the last occurrence of “Banana” in the range.
Does XLOOKUP work with wildcards?
Yes, but you must set match_mode to 2. Then you can use * and ? in your lookup_value.
Can XLOOKUP replace VLOOKUP entirely?
For most tasks, yes. XLOOKUP is more flexible and easier to use. However, VLOOKUP still works in older Excel versions, and some users may have legacy spreadsheets that rely on it.