The XMATCH function

The =XMATCH(G3,B3:B12) formula in cell H3 of Figure 10.11 returns 9 because it found Stockton Beach in the ninth row of the B3:B12 range. The =XMATCH(G8,B3:B12) formula in cell H8 returns #N/A because Short Beach does not appear within cells B3:B12. Finally, the =XMATCH(G13,B2:E2) formula in cell H13 of Figure 10.11 returns 3 because it found Miles in the third column of the B2:E2 range. As you can see, XMATCH can look down rows or across columns, just like the MATCH function:

Figure 10.11 – The XMATCH function

The XMATCH function has four arguments:

• Lookup_value – What to look for.
• Lookup_array – A row, column, or array to search.
• Match_type – This optional argument offers the same four choices as XLOOKUP:
  • 0 – Exact match, or return #N/A
  • -1 – Exact match, or return the next smaller item
  • 1 – Exact match, or return the next larger item
  • 2 – Wildcard match using *, ?, or ~
...