How to use XLOOKUP in excel
What is XLOOKUP
XLOOKUP is a powerful function in Excel that allows you to search for values in a table and return related values. It can be used to replace VLOOKUP,INDEX/MATCH, and other excel lookup functions. With XLOOKUP, you can quickly find the value you’re looking for in a table, whether it’s a single value or an entire range. It’s especially useful when working with large datasets or complex tables.
XLOOKUP is a powerful new lookup function in Excel that is more flexible and easier to use than VLOOKUP and INDEX/MATCH. It can look up values in both rows and columns, and it can perform exact, approximate, or wildcard matches. XLOOKUP also has a number of other benefits, including:
- It can return an array of values, instead of just a single value.
- It can perform binary searches on sorted data, which makes it faster than VLOOKUP and INDEX/MATCH.
- It can return custom results if no match is found.
Working Syntax of XLOOKUP
The format of XLOOKUP is as follows:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Arguments of XLOOKUP
The first three arguments are mandatory, while the latter three are optional and offer additional control over the lookup process.
| Arguments | Description |
|---|---|
| lookup | [Required]The value to search for. |
| lookup_array | [Required]The array or range to search. |
| return_array | [Required]The array or range to return. |
| not_found | [optional] Value to return if no match found. |
| match_mode | [optional] 0 = exact match (default), |
| -1 = exact match or next smallest, | |
| 1 = exact match or next larger, | |
| 2 = wildcard match. | |
| search_mode | [optional] 1 = search from first (default), |
| -1 = search from last, | |
| 2 = binary search ascending, | |
| -2 = binary search descending. |
How XLOOKUP Outperforms VLOOKUP and Other Excel Lookup Functions?
Compared to traditional VLOOKUP or HLOOKUP, XLOOKUP offers several advantages.
No Need for a Sorted Lookup Row or Column: Unlike VLOOKUP, XLOOKUP does not necessitate a sorted lookup row or column. This freedom expedites data search and retrieval.
Two-way Lookup: XLOOKUP can look both vertically and horizontally. The restrictions of VLOOKUP’s vertical and HLOOKUP’s horizontal lookups become a thing of the past.
Return Entire Row or Column of Data: XLOOKUP can fetch an entire row or column of data, not just one value.
Customizable Search Order: This function allows you to search from first to last (default) or from last to first (reverse order).
XLOOKUP example – Simple lookup
In the below image we can see that we have a data set where in we wanted to lookup gross_amount for order_no 17221-22.So we used XLOOKUP to get this value. We choose lookup_value as order_no and then lookup_array as order_no column and return_array as gross_amount column.
Excel XLOOKUP example – LOOKUP in reverse
In the below example we can see that we wanted to get variant name for a particular order id. But when we see our dataset we observed that variant name is in before our lookup array. Unlike vlookup,here xlookup comes to rescue the issue.
XLOOKUP formula in excel with example – multiple columns lookup
In the below example we can see that in just using XLOOKUP formula in cell L16 values spread to next two columns as well. For this we just need to increase range in return_array. So here we use E3:G27 instead of using E3:E27. As soon as we close this formula values will spread for next consecutive columns as well basis the selected columns in return_array
XLOOKUP examples – XLOOKUP multiple criteria
Till now we were looking up value based on single criteria. But we also can use XLOOKUP function to lookup value with multiple criteria. In the below example we are trying to find values based variant_name and BILL DATE.
You can read more on on click here how to use xlookup.
Conclusion
XLOOKUP, inheriting the flexibility of the INDEX MATCH function and overcoming the limitations of VLOOKUP, thus emerges as a potent force in Excel’s suite of functions. The adoption of XLOOKUP can significantly enhance the functionality and ease of Excel-based tasks, elevating productivity and paving the way for seamless data management.