### XMATCH

The __XMATCH__ function returns the position of a value in a list, table or cell range.

This function replaces the __MATCH__ function.

This function defaults to "exact match" instead of "exact match or the next smallest item".

This function is not case sensitive by default but this can be changed using the "search_mode" argument.

#### Return the Exact Position (unique values)

This function will return the relative position of the "lookup_value" in the "lookup_array".

When the "match_mode" argument is 0 an exact match is found.

The list does not have to be sorted when looking for an exact match.

#### Return the Exact Position (item does not exist)

If the "lookup_value" is not in the list, #N/A is returned.

When the "match_mode" argument is 0 an exact match is found.

The list does not have to be sorted when looking for an exact match.

#### Return the Exact Position (case sensitive)

The XMATCH function is not case sensitive by default.

When the "match_mode" argument is 0 an exact match is found.

This means that upper case and lower case characters are equal (for example Tokyo = TOKYO).

#### Return the Exact Position (first occurrence, sorted or not sorted)

This function will always return the position of the first occurrence, starting at the top.

When the "match_mode" argument is 0 an exact match is found.

The list does not have to be sorted to return the first occurrence.

#### Return the Exact Position (last occurrence, not sorted)

You can get the last occurrence when the list is not sorted by using the __MAX__, __MIN__, __IF__ and __ROW__ functions.

This formula must be entered as an __Array Formula__ using (Ctrl + Shift + Enter).

The largest value from an array of matching row numbers.

You can swap the MIN function for __LARGE__ or __SMALL__ to return the nth largest or nth smallest positions.

#### Return the Exact Position (with wildcard *)

An asterisk (*) matches any sequence of characters.

The "match_mode" argument must be 0.

You can have an asterisk as the first character.

#### Return the Exact Position (with wildcard ?)

A question mark (?) matches any single character.

The "match_mode" argument must be 0.

You can have a question mark as the first character.

#### Return the Closest Position (next smallest)

When the "match_mode" argument is 1 the closest value, less than (or equal to) "lookup_value" is returned.

When the "match_mode" argument is 1 the function assumes the list is sorted into **Ascending Order**.

This list has been sorted into ascending order, smallest first (a-z).

#### Return the Closest Position (next largest)

When the "match_mode" argument is -1 the closest value, greater than (or equal to) "lookup_value" is returned.

When the "match_mode" argument is -1 the function assumes the list is sorted into **Descending Order**.

This list has been sorted into descending order, largest first (z-a).

#### Return the Closest Position (with wildcards)

You cannot have a wildcard character as your first character when the "match_mode" argument is not 0.

If your "lookup_value" begins with an asterisk and the "match_mode" argument is not 0, the "lookup_value" is taken to be a literal string.

If your "lookup_value" begins with a question mark and the "match_mode" argument is not 0, the "lookup_value" is taken to be a literal string.

When the "match_mode" argument is 1 the closest value, less than (or equal to) "lookup_value" is returned.

When the "match_mode" argument is 1 the function assumes the list is sorted into **Ascending Order**.

#### Combining with INDEX

You can use the __INDEX and MATCH__ functions as an alternative to using the __VLOOKUP__ or __HLOOKUP__ functions.

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext