MATCH Function

The MATCH function returns the position of a value in an array or list.
This function is not case sensitive.


Return the Exact Position

When the "match_type" argument is 0 an exact match is found.
This function will return the relative position of the "lookup_value" in the "lookup_array".
When looking for an exact match, the list does not have to be sorted.


Return the Exact Position (case sensitive)

When the "match_type" argument is 0 an exact match is found.
The MATCH 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 match by using the EXACT function.
This formula must be entered as an Array Formula using (Ctrl + Shift + Enter).


Return the Exact Position (more than one occurrence)

When the "match_type" argument is 0 an exact match is found.
The position returned by this function is always the first occurrence, starting at the top.
New York appears in this list twice but the position of the first item is always returned.


Return the Exact Position (item does not exist)

When the "match_type" argument is 0 an exact match is found.
If the "lookup_value" is not in the list then #N/A is returned.


Return the Exact Position (with wildcard ?)

A question mark (?) matches any single character.
When the "match_type" argument is 0 you can have a question mark as the first character.


Return the Exact Position (with wildcard *)

An asterisk (*) matches any sequence of characters.
When the "match_type" argument is 0 you can have an asterisk as the first character.


Return the Closest Position (ascending order)

When the "match_type" argument is 1 the closest value, less than (or equal to) "lookup_value" is returned.
When the "match_type" argument is 1 the function assumes the list is sorted in ascending order.
This list has been sorted into ascending order, smallest first.


Return the Closest Position (descending order)

When the "match_type" argument is -1 the closest value, greater than (or equal to) "lookup_value" is returned.
When the "match_type" argument is -1 the function assumes the list is sorted in descending order.
This list has been sorted into descending order, largest first.


Return the Closest Position (wildcard, ascending order)

You cannot have a wildcard character as your first character when your "match_type <> 0"
If your "lookup_value" begins with an asterisk or a question mark and the "match_type" is not 0 the "lookup_value" is taken to be a literal string.


Array Form



Combining with INDEX

You can use the INDEX and MATCH functions as an alternative to using the VLOOKUP or HLOOKUP functions.


Combining with VLOOKUP

You can use the VLOOKUP and MATCH functions to perform a two way lookup.


Combining with HLOOKUP

You can use the HLOOKUP and MATCH functions to perform a two way lookup.



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