XMATCH
Formulas / XMATCHFind 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?
What makes XMATCH better than the MATCH function?
What types of matches can be made with XMATCH?
What is the syntax of the XMATCH function?
- XMATCH(lookup_value, array, match_type)
What does the XMATCH function return?
