If you are looking to learn how to use XLOOKUP in Excel or how to properly write the XLOOKUP function, you’ve come to the right place. The XLOOKUP function is one of the most advanced and modern lookup functions in Excel and Google Sheets. It is a replacement for earlier formulas such as VLOOKUP and HLOOKUP, and helps with searching data in a much faster, accurate, easier way.
In this complete guide, you’ll learn how to use XLOOKUP formula step by step, including how to use XLOOKUP in Excel with two sheets, how to use XLOOKUP in Google Sheets, and even how to use XLOOKUP with multiple criteria. Whether you’re a beginner or someone who works with large datasets, this tutorial will help you master XLOOKUP in a simple and practical way.
What Is XLOOKUP?
XLOOKUP is a enhanced lookup function you can access in Excel and Google Sheets that searches for a value and returns related information from another column or row. It is more versatile than legacy functions such as VLOOKUP because it allows you to search both left and right, and defaults to an exact match. In short, XLOOKUP is a new and better way for accurately searching and returning the data.
XLOOKUP Syntax — Explained in Simple Terms
Here’s the basic structure of the XLOOKUP formula:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | What it means |
|---|---|
| lookup_value | The value you want to find |
| lookup_array | The column or range where you want to look |
| return_array | The column or range where the result comes from |
| if_not_found | (Optional) What to show if no match is found |
| match_mode | (Optional) Exact match, approximate, or wildcard match |
| search_mode | (Optional) Search first→last or last→first |
How to Use XLOOKUP in Excel — Basic Example
Imagine you have this table:
| Product ID | Price |
|---|---|
| P001 | $10 |
| P002 | $15 |
| P003 | $12 |
| P004 | $20 |
If you want to find the price of Product ID in cell E2, here’s the XLOOKUP formula:
=XLOOKUP(E2, A2:A5, B2:B5, “Not Found”)
This means:
“Look for the value in
E2inside the rangeA2:A5(Product IDs), and return the matching value fromB2:B5(Prices). If nothing is found, show ‘Not Found.’”
How to Use XLOOKUP in Excel With Two Sheets
Now imagine the product table is on a different sheet named Products.
Here’s how the formula looks:
=XLOOKUP(E2, Products!A2:A100, Products!B2:B100, “Not Found”)
| Sheet | Range |
|---|---|
| Products | A2:A100 (Lookup column) |
| Products | B2:B100 (Return column) |
XLOOKUP will find the value from the Products sheet and return the answer to your main sheet.
How to Use XLOOKUP in Google Sheets
Google Sheets supports XLOOKUP with the same structure as Excel:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
So if your table is in Google Sheets, and you want to look up a value, you can use the same formula:
=XLOOKUP(E2, A2:A5, B2:B5, “Not Found”)
The only difference in Google Sheets is that array behavior might behave slightly differently with large datasets, but the formula structure remains the same.
How to Use XLOOKUP With Multiple Criteria
Sometimes you need to search with more than one condition — like first name AND last name — and return an email, price, or ID.
Method A: CONCATENATE Keys
Add a helper column that joins both values:
| A | B | C |
|---|---|---|
| First Name | Last Name |
In the helper column D:
=A2 & ” ” & B2
Now use:
=XLOOKUP(G1 & ” ” & G2, D2:D50, C2:C50, “Not Found”)
This looks up first and last name together.
Method B: Boolean Logic
You can skip the helper column with a formula that creates a logical test:
=XLOOKUP(1, (A2:A50=G1)*(B2:B50=G2), C2:C50, “Not Found”)
Explanation:
(A2:A50=G1)returns TRUE/FALSE array(B2:B50=G2)returns TRUE/FALSE array- Multiplying them turns TRUE=1 and FALSE=0
- XLOOKUP looks for
1(all conditions met)
XLOOKUP With Multiple Return Columns
If you want to return more than one column at a time, you can do that too.
Example:
=XLOOKUP(E2, A2:A5, B2:D5, “Not Found”)
This returns values from three columns (B, C, and D) for the matching lookup.
Useful Match Options
| Match Mode | What it does |
|---|---|
| 0 | Exact match (default) |
| -1 | Exact or next smaller |
| 1 | Exact or next larger |
| 2 | Wildcard match (*, ?, ~) |
Example — approximate match:
=XLOOKUP(E2, A2:A10, B2:B10, , -1)
Perfect for price bands, grading thresholds, and rate tables.
Reverse Search — Last Match
If a value appears many times and you want the last result:
=XLOOKUP(E2, A2:A100, B2:B100, “Not Found”, 0, -1)
Search Mode = -1 tells XLOOKUP to search from last to first.
Common XLOOKUP Formula Examples
| Goal | Formula |
|---|---|
| Basic exact match | =XLOOKUP(E2, A2:A10, B2:B10) |
| Not found custom message | =XLOOKUP(E2, A2:A10, B2:B10, "No data") |
| Two sheets | =XLOOKUP(E2, Sheet2!A2:A50, Sheet2!B2:B50) |
| Multiple criteria (Boolean) | =XLOOKUP(1, (A2:A50=G1)*(B2:B50=G2), C2:C50) |
| Last match | =XLOOKUP(E2, A2:A100, B2:B100, , 0, -1) |
| Return multiple columns | =XLOOKUP(E2, A2:A10, B2:D10) |
Why XLOOKUP Is Better Than VLOOKUP
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Search left? | Yes | No |
| Return multiple columns? | Yes | Only 1 |
| Default exact match | Yes | No |
| Easy two-way search | Yes | No |
| Requires sorted table | No (only for approximate matches) | Yes (approximate only) |
XLOOKUP gives you more options, simpler formulas, and fewer errors.
Common Mistakes Beginners Make
Using full column references (like A:A) with huge tables — this can slow performance
Forgetting to lock ranges with $ when copying formulas
Not handling missing matches — leading to #N/A errors
Using old Excel versions that don’t support XLOOKUP
Tips for Better Results
- Always set the if_not_found argument to error to prevent traceback errors
- Use named ranges for better readability in formulas
- Right convert data to Excel Table and make references dynamic
- Use sample data to check your XLOOKUP results before applying it on the real deal.
Conclusion
One of the most powerful lookup functions in existence today is one called XLOOKUP. So whether you’re working in Excel or Google Sheets right now, whether your data is on one sheet or spread across many, and whether you need to match a single value or the quagmire of multiple criteria, XLOOKUP has got us covered.
Also, Read About: How to Install 35‑ds3chipdus3: A Detailed Step‑by‑Step Guide