XLOOKUP Function in Excel 365 | Module 3 | Video 48

The XLOOKUP function in Excel is a new (as of September 2020) lookup and reference function that searches for a value in a range of cells, and then returns a value from a specified cell in the same row. It is similar to the VLOOKUP and HLOOKUP functions, but it has more flexibility and functionality.

For example, suppose you have a table of data with employee names and salaries in columns A and B, and you want to find the salary of a specific employee. You can use the XLOOKUP function to search for the employee name in column A, and then return the salary from column B.

Here is the syntax for the XLOOKUP function:

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

lookup_value: The value you want to look up.
lookup_array: The range of cells in which you want to search for the lookup_value.
return_array: The range of cells from which you want to return a value.
[if_not_found]: (optional) The value you want the function to return if the lookup_value is not found in the lookup_array. If this argument is omitted, the function will return the #N/A error if the lookup_value is not found.
[match_mode]: (optional) A number that specifies the type of match you want the function to perform. The possible values are:
1 (or omitted): The function will perform an exact match.
0: The function will perform an exact match, but it will consider a value to be a match if it is equal to the lookup_value after leading and trailing spaces are removed from the text.
-1: The function will perform an approximate match, and will return the value from the return_array that is closest to the lookup_value.
[search_mode]: (optional) A number that specifies how the function should search for the lookup_value in the lookup_array. The possible values are:
1 (or omitted): The function will search the lookup_array in ascending order.
-1: The function will search the lookup_array in descending order.
2: The function will search the lookup_array using a binary search algorithm.
The XLOOKUP function is more powerful and flexible than the VLOOKUP and HLOOKUP functions, because it allows you to specify the value to return if the lookup_value is not found, and it provides more options for matching and searching. It is also easier to use, because it does not require you to specify the column number from which to return a value.