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. |
Let us do it:
Example1: With no match mode and no search mode.
It picks 1st talk time of laptop entry
Example2:With condition if not found
Example3:With condition search from last
It picks value 86 from last as highlighted in red.
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.