# 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 "closest match or the next smallest" (which MATCH did).

This function **is not case sensitive**.

### 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. This is the default.

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. This is the default.

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

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

The XMATCH function **is not case sensitive**.

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

You can perform a case sensitive lookup by using the __EXACT__ function.

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

### 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. This is the default.

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.

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 text characters (not numbers).

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 text character (not numbers).

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.

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