How to use XLOOKUP in excel : 4 XLOOKUP example to make you awesome

How to use XLOOKUP in excel 


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.

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.

How to use XLOOKUP in excel
How to use XLOOKUP in excel 

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.

Excel XLOOKUP example - LOOKUP in reverse
Excel XLOOKUP example – LOOKUP in reverse

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 formula in excel with example
XLOOKUP formula in excel with example

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.

xlookup multiple criteria
xlookup multiple criteria

You can read more on on click here how to use xlookup.


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.

Leave a Reply