To Follow Along in the Video, you can download the companion Excel File. Download File

To lookup and retrieve data from another table/range. This is a very common scenario where you do not have all the data you need in one dataset. Therefore based on a value, you can use XLOOKUP to lookup and retrieve data from another dataset essentially combining data from two different datasets.

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

lookup_value – The value you want to search for in the first array

lookup_array – The array or range to search

return_array – The array or range to return

if_not_found – [Optional] A text string to return if no match is found. If not set, the value will display #N/A error if the lookup_value is not 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.

Function Return Value: Matching value(s) from the return_array

The Excel XLOOKUP function is a modern replacement for many other functions, including the VLOOKUP, HLOOKUP, and INDEX MATCH functions. It can find values vertically or horizontally, it is able to lookup to the left, and it can return entire rows or columns, not just one value.

The syntax of XLOOKUP is much more intuitive to use than the older functions. Instead of referencing the whole table and defining a result column index, XLOOKUP allows you to define the lookup_array and the return_array separately. The function searches a given lookup_value in the lookup_array, and returns a corresponding value or row from the return_array. Here is an example:

Replacement for HLOOKUP Function

In this example, we must match on both the first name and last name to lookup the proper value in the lookup_array. Notice in the formula the usage of the Ampersand(&).

In this example, we want to return both Test 1 and Test 2. So instead of just returning one value, we want to return two values. Note: You can return as many values as desired. The returned array will spill into the adjacent cells of the destination cell.