Function XLOOKUP()

XLOOKUP function in Microsoft Excel allows users to look up and retrieve data from a table based on a specified search term.

It can perform:

    • Exact match
    • Approximate match
    • wildcard matches.

And it can return results from a single column or multiple columns.

The XLOOKUP function has several arguments, including the lookup value, the lookup array, the return array, and optional arguments for specifying the match type and search mode


XLOOKUP (lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)


It can be used for a variety of tasks, such as finding a value in a table, retrieving data based on multiple criteria, and creating dynamic ranges.

 lookup_value  The value to search for Mandatory
 lookup_array  The range to search  Mandatory
 return_array  The range to return Mandatory
 [if_not_found]  If a valid match is not found function will return the text you provide here. If no text is provided, it displays #N/A  Optional
[match_mode] Specify the match mode here: (0, -1, 1 or 2)
0 – exact match. If none is found, return #N/A. This is the default.
-1 – Exact match. If none is found, return the next smaller item.
1 – Exact match. If none is found, return the next larger item.
– A wildcard match where *, ?, and ~ have special meaning. (See the table below)


Wild card character meaning:
?          Any single character
*          Any number of characters
~ followed by *, ? or ~

Please publish modules in offcanvas position.