XMATCH

Formulas / XMATCH
Find where an item is in a list or table.
=XMATCH(lookup_value, lookup_array, 0, 1/-1), XMATCH(lookup_value, lookup_array, -1, 1/-1), XMATCH(lookup_value, lookup_array, 1, 1/-1)
  • lookup_value - required, lookup value
  • lookup_array - required, array or range to search
  • match_mode - [OPTIONAL], match type
  • search_mode - [OPTIONAL], search type

Examples

  • =XMATCH(G5,B6:B14)

    The XMATCH function is used to search for a value within a given range of cells, and returns the relative position of the value within the range. For example, this searches the range B6 to B14 for the value in G5. If the value is found, the XMATCH function will return the relative position of the value, in this case 4.

  • =XMATCH("apple",B6:B14,D6:D14)

    The XMATCH function can also be used to search for a value within a given range of cells, and returns a value from a different range of cells. For example, if cell G5 contains the value "apple" and you want to find the corresponding value in cell D5, you can use the formula above. This will search the range from B6 to B14 for the value "apple" and return the corresponding value from the range D6 to D14.

  • =XMATCH("apple",B6:B14,D6:D14,1)

    The XMATCH function can also be used to search for a value within a given range of cells, and returns an exact match or an approximate match. For example, if cell G5 contains the value "apple" and you want to find the corresponding value in cell D5, you can use the formula above. This will search the range from B6 to B14 for the exact value "apple" and return the corresponding value from the range D6 to D14.

  • =XMATCH("apll",B6:B14,D6:D14,-1)

    The XMATCH function can also be used to search for a value within a given range of cells, and returns an exact match or an approximate match. For example, if cell G5 contains the value "apll" and you want to find the closest match in cell D5, you can use the formula above. This will search the range from B6 to B14 for the closest match to "apll".


Summary

The XMATCH function is a useful tool for finding the position of an item in an array or range of cells. It searches for the item and returns its position relative to the start of the array or range.

  • XMATCH performs a lookup and returns a position. It is more powerful than MATCH, supporting approximate and exact matches, and accepts wildcards for partial matching.

Frequently Asked Questions

What is the XMATCH function?
The XMATCH function is an improved version of the MATCH function in Sourcetable. It is available in Excel for Microsoft 365 and Excel 2021.
What makes XMATCH better than the MATCH function?
The XMATCH function has several advantages over MATCH. It can search in both first-to-last and last-to-first directions, it can find exact, approximate, and partial matches, and it can return the relative position of a value in an array or range of cells.
What types of matches can be made with XMATCH?
XMATCH can make exact, approximate, and partial matches. It can also search in both first-to-last and last-to-first directions.
What is the syntax of the XMATCH function?
The syntax of the XMATCH function is as follows:
  • XMATCH(lookup_value, array, match_type)
What does the XMATCH function return?
The XMATCH function returns the relative position of a value in an array or range of cells.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV