XMATCH Function in Excel

The XMATCH function in Excel is used to perform a lookup and return the position of a value in a range. XMATCH supports both exact match and approximate match searches, and returns the relative position of the value within the range.

The syntax for XMATCH is:

=XMATCH(lookup_value, lookup_array, [match_type])

where:

• lookup_value: is the value you want to look up
• lookup_array: is the range of cells that contains the data you want to look up
• match_type: is the match type, either exact match (0), less than (-1), or greater than (1)

For example, if you have a table of data in range A1:A5 and you want to look up the position of a value in column A, the XMATCH formula for an exact match would be:

=XMATCH(A7, A1:A5, 0)

In this formula, A7 is the lookup value, A1:A5 is the lookup array, and 0 is the match type, specifying an exact match. The result of this formula would be the relative position of A7 within the range A1:A5.

If you want to perform an approximate match, the XMATCH formula for the nearest value less than the lookup value would be:

=XMATCH(A7, A1:A5, -1)

In this formula, A7 is the lookup value, A1:A5 is the lookup array, and -1 is the match type, specifying the nearest value less than the lookup value. The result of this formula would be the relative position of the nearest value less than A7 within the range A1:A5.