Here, we will learn how to use the Excel dynamic array functions: XLOOKUP and XMATCH.
XLOOKUP function
The Excel XLOOKUP function searches for the given item in the given array or a range, and then returns another item corresponding to the first match it finds. The function can be found in Excel 365 - Excel 2021. XLOOKUP takes the arguments like this: (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]), of which lookup_value, lookup_array and return_array are required. Lookup_value accepts the lookup value to search for. Lookup_array accepts the array or range to search in. Return_array accepts the array or range from which to extract the corresponding item. If_not_found accepts a custom value to use when no match is found. Omitting the argument will force the function to return the #N/A error. Match_mode controls the match type. It accepts 0 to perform exact match, -1 to perform exact match or next smallest, 1 to perform exact match or next larger or 2 to perform wildcard match. If the argument is skipped, it defaults to 0. Search_mode controls the direction of search. It accepts 1 to search from first, -1 to search from last, 2 for binary search on data sorted ascending or -2 for binary search on data sorted descending. If the argument is skipped, it defaults to 1. The formula in F4, is:
=XLOOKUP(E4,A4:A11,C4:C11)
XMATCH function
The Excel XMATCH function searches for the given item in the given array or range, and then returns the numeric position of the first match. The function can be found in Excel 365 - Excel 2021. XMATCH takes the arguments like this: (lookup_value, lookup_array, [match_mode], [search_mode]), of which lookup_value and lookup_array are required. Lookup_value accepts the lookup value to search for. Lookup_array accepts the array or range to search in. Match_mode controls the match type. It accepts 0 to perform exact match, -1 to perform exact match or next smallest, 1 to perform exact match or next larger or 2 to perform wildcard match. If the argument is skipped, it defaults to 0. Search_mode controls the direction of search. It accepts 1 to search from first, -1 to search from last, 2 for binary search on data sorted ascending or -2 for binary search on data sorted descending. If the argument is skipped, it defaults to 1. The formula in M4, is:
=XMATCH(M3,H4:H11)
You have successfully learnt how to use the Excel dynamic array functions: XLOOKUP and XMATCH.
