How to use Xmatch function in Excel

The XMATCH function in Microsoft Excel is used to search for a specified value in a range of cells and return its relative position. The XMATCH function is similar to the MATCH function, but it provides more flexibility in terms of the match type and handling of errors. To use the XMATCH function in Excel, follow these steps:

  1. Type =XMATCH( in the cell where you want to return the result.
  2. Enter the value you want to search for, enclosed in double quotes or a reference to the cell that contains the value.
  3. Enter a comma, followed by the range of cells where you want to search for the value.
  4. Enter a comma and choose the match type: -1 for exact match 0 for approximate match 1 for exact match or next smaller value
  5. Add another comma and type the optional argument "match_mode" to specify how to handle errors. For example: "X" to return the #N/A error if no match is found "U" to return #N/A if an exact match is not found
  6. Close the parentheses and press Enter. The XMATCH function will return the relative position of the specified value in the range.

For example, the formula =XMATCH("apple", A1:A10, 0, "U") will search for the value "apple" in the range A1:A10 and return its relative position, or #N/A if no approximate match is found.

Note: The XMATCH function is only available in Excel 2013 and later versions.