Microsoft have finally done it… created the XLOOKUP. The Index match versus VLOOKUP argument has been one that has boiled for a while now. Now the XLOOKUP function will lookup in multiple directions and more. With the newest lookup feature the default is an exact match unlike VLOOKUP where you have to specify the match type which let’s be clear was mostly the exact match anyways.
The Syntax (very important) is as follows:
XLOOKUP( Lookup_value, Lookup_array, Return_array, [match_mode] , [match_mode] )
▲ Lookup_value – value to lookup
▲ Lookup_array – lookup column, the column where the lookup value is stored.
▲ [Not found] - [optional] Return value to return if no match found.
▲ Return_array – return column, what you are looking to return
▲ [match_mode] – 0 for exact match by default.
▲ [match_mode] – 1 for first to last match.
The last two arguments in square brackets are optional.
The XLOOKUP will quickly become the default when attempting to find items in a table or range by row. In the following example, look up the cost centre (CC) based on the country.
=XLOOKUP(C2,C5:C13,B5:B13)
So the formula asks for:
▲ What you are looking for, cell to look up.
▲ Where that cell is found in a list (range)
▲ The corresponding list, same row, different column.
If the item can not be found NA is returned. This is a departure from VLOOKUP where 0 or FALSE would need to be entered as the fourth argument.
The following example looks in column D for the grade entered in cell C2 and finds a matching marking grade in column C. It uses the match_mode argument set to 1, which means that the function will look for an what is know as an exact match, in the event where there is no match returned, it will return the next larger item.
In the Example above 82 is the figure searched for, as 82 falls after 80 but before the 85 then A will be returned for the grade. The formula is as follows.
=XLOOKUP(C2,C5:C12,D5:D12,1,1)
Where C2 is the lookup item.
▲ Where C2 is the lookup item.
▲ Cells C5 to C12 is where the mark hierarchy is.
▲ Cells C5 to C12 contain the appropriate grade.
▲ 1 for an exact match or next smallest item.
▲ 1 search first to last (This is the same as VLOOKUP where it tries to find an exact match starting at the very top and working down the list.
Enjoy working with XLOOKUP - you might have to wait a little while till everyone is on board but it will be worth it in the end.
