MATCH

MATCH(lookup_value, lookup_array [,match_type])

Returns the position of a value in a list, table or cell range.

lookup_value	The value you want to find.
lookup_array	The single column (or single row) of cells containing possible lookup values.
match_type	(Optional) The number specifying the type of match performed: 1 (> 0) = Closest match, exact or closest value less than it (default) 0 = Exact match, the lookup_array does not have to be sorted -1 (< 0) = Closest match, exact or closest value greater than it

REMARKS

* XMATCH was added in Excel 2021 to replace this function.
* For an illustrated example refer to the page under Lookup Functions
* Before you upgrade to XMATCH you need to read MATCH vs XMATCH
* The XMATCH function has different "match_type" numbers, -1 is now 1 and 1 is now -1.
* This function is not case sensitive when searching for text strings.
* This function supports wildcards (? and *).
* Wildcard character: ? = a single character.
* Wildcard character: * = multiple characters.
* To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~).
* The 'lookup_value' can be a value (number, text, or logical value) or a cell reference.
* If 'lookup_value' cannot be found and 'match_type' = 0, then #N/A is returned.
* If 'lookup_array' references more than a single column (or row) of cells, then #N/A is returned.
* If 'lookup_array' references a multi dimensional array, then #N/A is returned.
* If 'match_type' = 1 then 'lookup_array' must be sorted into ascending order (-1, 0, 1, a, A, FALSE, TRUE).
* If 'match_type' = 1 and 'lookup_value' cannot be found, the closest value less than it is returned.
* If 'match_type' = 1 and 'lookup_value' is smaller than the smallest value in the first column, then #N/A is returned.
* If 'match_type' = 1 and 'lookup_value' is larger than the largest value in the first column, then the largest value is returned.
* If 'match_type' = 0 then 'lookup_array' does not have to be sorted.
* If 'match_type' = 0 and 'lookup_value' does not exist, then #N/A is returned
* If 'match_type' = -1 then 'lookup_array' must be sorted into descending order (TRUE, FALSE, A, a, 1, 0, -1).
* If 'match_type' = -1 and 'lookup_value' cannot be found, the closest value greater than it is returned.
* If 'match_type' = -1 and 'lookup_value' is smaller than the smallest value in the first column, then the largest value is returned.
* If 'match_type' = -1 and 'lookup_value' is larger than the largest value in the first column, then #N/A is returned.
* You can use the XLOOKUP function to return a value in the same row (or column).
* You can use the HLOOKUP function to return a value in the same column.
* You can use the VLOOKUP function to return a value in the same row.
* You can use the LOOKUP function to return a value in a row (or column) that matches a value in a column (or row).
* You can use the CHOOSE function to return the value in a list based on an index number.
* You can use the INDEX and MATCH combination to lookup a value in any column.
* You can use the OFFSET and MATCH combination to lookup a value in any column.
* You can use the IFNA function to return the value or something else if it evaluates to #N/A.
* This function has been available since Excel 1997.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B
1	=MATCH("Wheels", B1:B3, 1) = 3	Bearings
2	=MATCH("Wheels", B1:B3, TRUE) = 3	Bolts
3	=MATCH("Bearings", B1:B3, -1) = 1	Wheels
4	=MATCH("Bearings", B1:B3, FALSE) = 1
5	=MATCH("NoMatch", B1:B3, 1) = 2	Wheels
6	=MATCH("NoMatch", B1:B3, FALSE) = #N/A	Bolts
7	=MATCH("Bear", B1:B3, 1) = #N/A	Bearings
8	=MATCH("Young", B5:B7, -1) = #N/A

1 - What is the position of the value "Wheels" in the range "B1:B3". Closest match.
2 - What is the position of the value "Wheels" in the range "B1:B3". Closest match. True = 1 which is > 0.
3 - What is the position of the value "Bearings" in the range "B1:B3". Closest match.
4 - What is the position of the value "Bearings" in the range "B1:B3". Exact match. False = 0.
5 - What is the position of the value "NoMatch" in the range "B1:B3". Closest match. Column is in ascending order.
6 - What is the position of the value "NoMatch" in the range "B1:B3". Exact match. There is no match.
7 - What is the position of the value "Bear" in the range "B1:B3". Closest match. Column is in ascending order. Smaller than the smallest value.
8 - What is the position of the value "Young" in the range "B5:B7". Closest match. Column is in descending order. Larger than the largest value.