The XLOOKUP function in Excel is a modern, flexible lookup tool. It searches a range or array for a value and returns a corresponding value from another range. Unlike VLOOKUP and HLOOKUP, XLOOKUP can look both vertically and horizontally, and it can search from top-to-bottom or bottom-to-top.
What is the XLOOKUP Function?
XLOOKUP is an improved replacement for older lookup functions. It removes many limitations of VLOOKUP and HLOOKUP, such as the inability to search to the left or above, and the need to count column or row numbers.
It works with vertical lists, horizontal rows, and even two-dimensional tables.
Syntax of the XLOOKUP Function:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: The value to search for.
- lookup_array: The range or array to search in.
- return_array: The range or array to return the result from.
- [if_not_found] (optional): The value to return if nothing is found.
- [match_mode](optional):
- 0 → exact match (default)
- -1 → exact match or next smaller item
- 1 → exact match or next larger item
- 2 → wildcard match (*, ?, ~)
- [search_mode](optional):
- 1 → search first-to-last (default)
- -1 → search last-to-first
Where Can We Use the XLOOKUP Function?
The XLOOKUP function is helpful in scenarios such as:
- Replacing VLOOKUP and HLOOKUP: One function for both vertical and horizontal searches.
- Looking to the Left or Above: No need to rearrange data.
- Exact or Closest Matches: Useful for ranges like prices or grades.
- Error Handling: Built-in
[if_not_found]option. - Dynamic Data Retrieval: Works well with large, changing datasets.
How to Use the XLOOKUP Function in Different Methods
Here are four common methods to use XLOOKUP in Excel:
Method 1: Basic Exact Match Lookup
This method returns an exact match for your lookup value.
Steps:
- Enter your data:
| A (Product ID) | B (Product Name) | C (Price) |
|---|---|---|
| 101 | Apple | 1.5 |
| 102 | Banana | 0.8 |
| 103 | Orange | 1.2 |
- In cell E1, type the Product ID (e.g.,
102). - In cell F1, type:
=XLOOKUP(E1, A2:A4, C2:C4) - Press Enter. The result (0.8) will appear.
Method 2: Returning Values to the Left
Unlike VLOOKUP, XLOOKUP can return a value from a column to the left of your lookup column.
Steps:
- Use the same table above.
- In E1, type the Price (e.g.,
1.2). - In F1, type:
=XLOOKUP(E1, C2:C4, B2:B4) - Press Enter. The result (Orange) will appear.
Method 3: Using the [if_not_found] Argument
You can directly handle missing values without using IFERROR.
Steps:
- Use the same table from Method 1.
- In E1, type a Product ID that doesn’t exist (e.g.,
999). - In F1, type:
=XLOOKUP(E1, A2:A4, C2:C4, "Not Found") - Press Enter. The result (“Not Found”) will appear.
Method 4: Horizontal Lookup with XLOOKUP
XLOOKUP can also search in rows like HLOOKUP.
Steps:
- Enter your data:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Apple | Banana | Orange |
| 2 | Price | 1.5 | 0.8 | 1.2 |
- In F1, type the fruit name (e.g.,
Banana). - In G1, type:
=XLOOKUP(F1, B1:D1, B2:D2) - Press Enter. The result (0.8) will appear.
Live Example Showing Progress in Each Step
Data Setup:
| A (ID) | B (Name) | C (Price) |
|---|---|---|
| 101 | Apple | 1.5 |
| 102 | Banana | 0.8 |
| 103 | Orange | 1.2 |
Goal:
Retrieve names or prices dynamically.
Step 1: Enter the table in A1:C4.
Step 2: Enter lookup values in Column E.
Step 3: Apply XLOOKUP formulas in Column F.
Final Table:
| Lookup Value | Result | Formula |
|---|---|---|
| 102 | 0.8 | =XLOOKUP(E2, A2:A4, C2:C4) |
| 1.2 | Orange | =XLOOKUP(E3, C2:C4, B2:B4) |
| 999 | Not Found | =XLOOKUP(E4, A2:A4, C2:C4, “Not Found”) |
Key Notes About the XLOOKUP Function
- Replaces VLOOKUP and HLOOKUP: Works in both directions.
- No Need to Count Columns/Rows: You directly select the return range.
- Can Search Left or Up: More flexible than older functions.
- Error Handling Built In: Use
[if_not_found]without extra formulas. - Advanced Options: Match mode and search mode make it powerful for custom lookups.
Conclusion
The XLOOKUP function in Excel is a modern, all-in-one lookup tool that’s easier to use and more powerful than VLOOKUP and HLOOKUP. Whether you’re looking up data vertically, horizontally, to the left, or above, XLOOKUP handles it all without complex formulas.
By mastering XLOOKUP, you can simplify your work, reduce errors, and handle a wider range of lookup situations. Practice with real-world datasets to fully unlock the benefits of this versatile function.
Leave a Reply