At a glance, this seems like a difficult problem because XMATCH only has one value forlookup_valueandlookup_array.
How can we configure XMATCH to consider values frommultiplecolumns?
This approach is explained below.
The XMATCH function
The XMATCH function is an upgraded replacement for the olderMATCH function.
This is the array used inside XMATCH as thelookup_array.
Because thelookup_arraycontains only 1s and 0s, we provide 1 as thelookup_value.
The screen below shows how the arrays can be visualized with simple formulas in a worksheet.
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
XLOOKUP supports approximate and exact matching, wildcards (* ?)
you could use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
