top of page

Excel's New XLOOKUP Function

Updated: Jun 28, 2023

XLOOKUP is a function in Microsoft Excel that allows you to look up a value in a range of cells and return a corresponding value from a different range of cells.


It is similar to Excel's VLOOKUP function, but has additional capabilities and is considered to be more flexible and powerful.

XLOOKUP Syntax


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


Xlookup Excel

The lookup_value (required) is the value that you want to find in the lookup array.


The lookup_array (required) is the range of cells that you want to search for the lookup value.


The return_array (required) is the range of cells that contains the values that you want to return.


The [if_not_found] (optional) argument is optional and specifies the value to return if the lookup value is not found in the lookup array.


The [match_mode] (optional) argument is optional and specifies how the XLOOKUP function should compare the lookup value to the values in the lookup array.

Specify the match type:

0 Exact match. If none found, return # N/A. This is the default.

-1 Exact match. If none found, return the next smaller item.

1 Exact match. If none found, return the next larger item.

2 A wildcard match where *, ?, and ~ have special meaning.


The [search_mode] (optional)

Specify the search mode to use:

1 Perform a search starting at the first item. This is the default.

-1 Perform a reverse search starting at the last item.

2 Perform a binary search that relies on lookup array being sorted in ascending order. If not sorted, invalid results will be returned.

-2 Perform a binary search that relies on lookup array being sorted in descending order. If not sorted, invalid results will be returned.


Here is an example of how to use the XLOOKUP function using the minimum required parameters:


=XLOOKUP(A2, A5:A9, B5:B9)


In this example, the XLOOKUP function will search for the value in cell A2 in the range of cells A5:A9. If a match is found, the corresponding value from the range B5:B9 will be returned.

If no match is found, an # N/A error* will be returned.



Comdex Training Centre 2022

 

See also our blog discussion on XLOOKUP Vs. VLOOKUP...

and related article on *Excel Errors


All our Excel articles can be found here


148 views1 comment

Recent Posts

See All

1 comentário


Convidado:
31 de jan.

When I learned about this function in Excel, it became much more convenient and faster for me to work with numbers, I began to use it in my work as an analyst. I can record a video of my screen using this free recorder to show you all the capabilities of this feature to optimize your work even further.

Curtir
bottom of page