What is XLOOKUP Function in MS Excel?

XLOOKUP function in MS Excel is like an upgraded form of the previous LOOKUP, VLOOKUP, and HLOOKUP functions. It is also used to find specific values from a range of cells in a worksheet in MS Excel.

Purpose of XLOOKUP Function

The purpose of the XLOOKUP function is that it can be used to find out both exact and approximate matches in cells. It can look up specific data in both horizontal and vertical ranges. Wildcard entries like * and ? can also be found using this function.

Syntax of VLOOKUP Function

The syntax of this function is given below.

=XLOOKUP(lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])

Arguments

• Lookup – The value you want to look for is given in this argument.
• Lookup_array – The array or range in the sheet you want to search is given in this argument.
• Return_array – The array or range to be returned from the rows and columns.
• Not_found – [optional] This value is returned if no match is 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

Example

For example, if you want to retrieve the total sales of different movies, the formula will be written as follows. B5-B9 is the cell address of movies, while E5-E9 is the cell address of total sales.

=XLOOKUP (H4, B5:B9, E5:E9)