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:

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.
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
XLOOKUP

Example2:With condition if not found

XLookup vs vlookup
Xlookup

Example3:With condition search from last

It picks value 86 from last as highlighted in red.

Xlookup
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.

Notes:

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.

https://exceljet.net/excel-functions/excel-xlookup-function

https://trumpexcel.com/xlookup-function/

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

Leave a Comment

Your email address will not be published. Required fields are marked *