You want to ride the track with a super power?! Go get XLOOKUP, it will definitely serve you well.
The XLOOKUP function in Excel is a versatile and powerful tool designed to replace older functions like VLOOKUP, HLOOKUP, and LOOKUP. It offers more flexibility and functionality, making it a favorite among Excel users for data retrieval tasks. Let’s explore the XLOOKUP function, its syntax, and how you can use it effectively. In a simple summary it doesn’t require you to provide how many rows or columns to skip once match is found.
What is the XLOOKUP Function?
The XLOOKUP function searches a range or an array and returns the item corresponding to the first match it finds. If no match exists, XLOOKUP can return the closest (approximate) match, making it highly adaptable for various lookup Scenarios.
Syntax and Arguments (boring stuff)
The syntax for the XLOOKUP function is as follows:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value: The value you want to search for.
- lookup_array: The range or array to search within.
- return_array: The range or array from which to return a value.
- if_not_found: Optional. The value to return if no match is found.
- match_mode: Optional. Specifies the type of match:
- 0: Exact match (default).
- -1: Exact match or next smaller item.
- 1: Exact match or next larger item.
- 2: Wildcard match.
- search_mode: Optional. Specifies the search mode:
- 1: Search from first to last (default).
- -1: Search from last to first.
- 2: Binary search (ascending order).
- -2: Binary search (descending order).
Practical Examples – now lets get to the meat of that burger!
Example 1: Basic Exact Match
Suppose you have a list of products and their prices. You want to find the price of a specific product.
=XLOOKUP("Product1", A2:A4, B2:B4)This formula searches for “Product1” in the range A2:A4 and returns the corresponding value from the range B2:B4, which is 15 in our case
Or you can always substitute “Product1” in the formula with a cell address where Product1 is written, like below where Product1 is located in cell A8:
Example 2: Handling Missing Values
If you want to return a custom message when a product is not found:
=XLOOKUP("Product4", A2:A4, B2:B4, "No such burger!")
Example 3: Approximate Match
To find the closest match for a given value:
=XLOOKUP("Product1.5",A2:A4,B2:B4, , 1)This formula searches for the value Product1.5 in the range A2:A4 and returns the closest larger value from the range B2:B4. In our case the closes values was Product 2, which has a price of 10. You can also do it with numerical values in column A.
Example 4: Multiple Return Values
To return multiple values (e.g., product name and price) from a single lookup:
=XLOOKUP("Product1", A2:A4, B2:C4)Now look at that! This formula returns for the Product1 the price and quantity from columns B and C.
Tips for Using XLOOKUP
- Use Named Ranges: To make your formulas more readable, use named ranges for your lookup and return arrays.
- Combine with Other Functions: XLOOKUP can be combined with other functions like SUM, AVERAGE, and IF to create more complex formulas.
- Leverage Wildcards: Use wildcards (* and ?) in the
match_modeargument to perform partial matches. - You can use XLOOKUP for more complex formulas with arrays! But how often do you need to do that? In my business analyst career at major IT company, I have used array formula only a couple of times in the last 15 years. Sometimes people want to have a simple life – that’s why I am not pasting any examples here. But let me know in the comments, if arrays are very dear to you and will expand my description.
Conclusion
The XLOOKUP function is a game-changer for Excel users, offering enhanced flexibility and functionality over older lookup functions. By mastering XLOOKUP, you can perform more efficient and powerful data retrieval tasks, making your data analysis and reporting more effective.
Check my other article on similar topics: Excel CUBEVALUE vs SUMIFS & GETPIVOTDATA » Data Empower