Understanding XLOOKUP in Excel: A Comprehensive Guide
Excel’s XLOOKUP function has evolved as a modern replacement for VLOOKUP and HLOOKUP, combining their capabilities and offering enhanced features. In this article, I will explore the concept of XLOOKUP in Excel, its syntax, and its practical applications.
What is XLOOKUP in Excel?
XLOOKUP is a versatile and robust function introduced in Excel 365 and Excel 2021, making it unavailable in earlier versions. It stands for “eXtended LOOKUP” and addresses many limitations of its predecessors, VLOOKUP and HLOOKUP. The XLOOKUP function allows users to search for a value in a specified range and return a related value from another column or row, regardless of their relative positions. Its dynamic nature, simplicity, and improved error handling set it apart as a powerful tool for data analysis and retrieval.
Syntax of XLOOKUP Function
The syntax of 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 in the lookup_array.
- lookup_array: The range of cells that contains the data to search for the lookup_value.
- return_array: The range from which to retrieve the corresponding value based on the lookup result.
- [if_not_found]: An optional parameter that allows you to specify the value to return if no match is found. If omitted, Excel will return an error or #N/A.
- [match_mode]: An optional parameter that determines the type of match to be performed. It can take values: 0 (Exact match – Default), -1 (Exact match or next smaller item), and 1 (Exact match or next larger item).
- [search_mode]: An optional parameter that defines the search direction. It can take values: 1 (Search from top to bottom – Default) and -1 (Search from bottom to top).
Vertical Lookup with XLOOKUP Function
Let’s look for the age against the ID number 45. We can easily accomplish this using the VLOOKUP function. But here we will try to XLOOKUP function instead.
Anyways, here’s the lookup formula:
=XLOOKUP(C12,A2:A10,D2:D10)This formula returns 29 which is the age against the ID number 45.
Formula Breakdown
- C12: It’s the lookup value that refers to the ID number 45.
- A2:A10: This is a lookup array. It’s the range where the formula will search for the lookup value.
- D2:D10: This is the range of the cells from where we want to retrieve data.
Horizontal Lookup with XLOOKUP Function
Now, let’s look for the marks in Biology against the ID number 70. We can accomplish this easily using the HLOOKUP function. But here we will try the XLOOKUP function instead.
Anyways, here’s the lookup formula:
=XLOOKUP(E8,B1:G1,B6:G6)This formula returns 86 which is the marks in Biology against the ID number 70.
Formula Breakdown
- E8: It’s the lookup value that refers to the ID number 70.
- B1:G1: This is a lookup array. It’s the range where the formula will search for the lookup value.
- B6:G6: This is the range of the cells from where we want to retrieve data.
Error Handling with the XLOOKUP Function
The XLOOKUP function comes with a default feature that allows us to show a custom message when a particular error occurs. So, let’s see how to utilize this feature.
In the following screenshot, you can see that the XLOOKUP formula returns a #N/A error as the search ID number 75 doesn’t exist.
Now, we are gonna show a different message instead of this built-in error message using the 4th argument of the XLOOKUP function.
The 4th argument of the XLOOKUP function is [if_not_found] which is an optional argument. Now, look at the following formula using which I displayed a custom message instead of the #N/A error.
=XLOOKUP(E8,B1:G1,B6:G6,"No Record Found!")
Exploring Match Modes of the XLOOKUP Function in Excel
The 5th argument of the XLOOKUP function allows you to select one of the 3 match modes. Those are:
- 0: Exact match | Default
- -1: Exact match or next smaller item
- 1: Exact match or next larger item
If this argument is skipped or set to 0, XLOOKUP will look for an exact match. See the screenshot below:
Setting the 5th argument to -1 will look for an exact match first. If no match is found, it will look for the immediate smaller item. See an example in the screenshot below:
Here, ID number 65 doesn’t exist. Its immediate smaller ID number is 60. So, XLOOKUP returns the marks in Biology against the ID number 60 which is 94.
Finally, setting the 5th argument to 1 will prompt XLOOKUP to look for an exact match or the immediate larger value than the lookup value. Here’s an example:
Exploring Search Modes of the XLOOKUP Function in Excel
The XLOOKUP function offers 4 search modes with its 6th argument, [search_mode] which is an optional argument. Here are the 4 search modes of it:
- 1: Search first-to-last
- -1: Search last-to-fast
- 2: Binary search (sorted in ascending order)
- -2: Binary search (sorted in descending order)
Let’s say we have to 2 duplicate items in the lookup array. Now which one will be picked by the XLOOKUP function, the first one or the second one? Well, the seach_mode argument resolves this problem.
If we set this argument to 1, it will pick up the first entity. Here’s an example:
Setting the 6th argument to -1 will pick up the last entity. See screenshot:
Advantages of XLOOKUP over VLOOKUP and HLOOKUP
XLOOKUP offers several advantages that make it a preferred choice over VLOOKUP and HLOOKUP:
- Two-way Lookup: Unlike VLOOKUP and HLOOKUP, XLOOKUP can perform both vertical and horizontal lookups. It allows users to search and retrieve data from any direction within the dataset.
- Simplicity: The XLOOKUP function’s simplified syntax makes it easier to use and understand, even for users with limited Excel experience.
- Enhanced Error Handling: XLOOKUP handles errors more efficiently and offers better control over handling situations where no match is found, reducing the likelihood of unintended results.
- Dynamic Range Selection: XLOOKUP automatically adjusts the range when inserting or deleting columns/rows, eliminating the need to update the formula manually.
Conclusion
XLOOKUP has revolutionized the way data professionals perform lookups in Excel. Its versatility, dynamic range selection, and improved error handling make it a powerful tool for data analysis and retrieval. By understanding its syntax and practical applications, you can harness the full potential of XLOOKUP to streamline your data-related tasks and elevate your Excel experience to new heights.
