How to use XLOOKUP in excel: Understanding it with 3 examples

How to use XLOOKUP in excel

XLOOKUP is used to search a range or an array and then return a value matching to the condition irrespective of the column direction. It is an extension of vlookup wherein we could get value from one direction only. XLOOKUP has more flexibility with itself as compared to vlookup.

Let us understand the syntax of XLOOKUP:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Arguments of XLOOKUP:

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.
Description of arguments

Let us do it:

Example1: With no match mode and no search mode.

It picks 1st talk time of laptop entry

How to use XLOOKUP in excel

Example2:With condition if not found

XLookup vs vlookup

Example3:With condition search from last

It picks value 86 from last as highlighted in red.

search from last

Benefits of XLOOKUP:

It can search value from last to first as well as first to last,

Can search value in both direction

can pick horizonal as well as vertically value by nesting XLOOKUP

Can return entire row or column and not only value.


If used between workbooks, both workbooks must be open, otherwise XLOOKUP will return #REF!.

It works in OFFICE365 version only.

You can read on XLOOKUP from the below articles.

You can also read about other look up like vlookup and hlookup from the link.

Leave a Comment