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 = Closest match, exact or the next smallest (lookup_array must be sorted in ascending order) (default) -1 = Closest match, exact or the next largest (lookup_array must be sorted in descending order) 0 = Exact match (lookup_array does not have to be sorted)

REMARKS

* XMATCH was added in Microsoft 365 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.
* The "lookup_value" is the value you want to match in the "lookup_array".
* 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" = 1, then the next smallest value is returned.
* If "lookup_value" cannot be found and "match_type" = -1, then the next largest value is returned.
* If "lookup_value" cannot be found and "match_type" = 0, then #N/A is returned.
* The "lookup_array" can be a cell reference or an array.
* If "lookup_array" references more than a single column of cells, then #N/A is returned.
* If "lookup_array" references more than a single 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, then "lookup_array" must be sorted into descending order (TRUE, FALSE, A, a, 1, 0, -1).
* If "match_type" = 0, then "lookup_array" does not have to be sorted.
* If "match_type" < 0, then, -1 is used.
* If "match_type" > 0, then 1 is used.
* You can use the XMATCH function to return the position of a value.
* 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 XLOOKUP function to return a value in the same row (or column).
* You can use the INDEX function to return the intersection of a row and a column.
* You can use the OFFSET function to return a value in an offset position.
* You can use the CHOOSE function to return the value in a list based on an index number.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C	D	E
1	=MATCH(10,B1:B7) = 2	5	35	Apple	Strawberry
2	=MATCH(10,B1:B7,1) = 2	10	30	Banana	Peach
3	=MATCH("Cherry",D1:D7) = 3	15	25	Cherry	Orange
4	=MATCH(13,B1:B7) = 2	20	20	Lemon	Lemon
5	=MATCH("Cherrys",D1:D7) = 3	25	15	Orange	Cherry
6	=MATCH(13,B1:B7,0) = #N/A	30	10	Peach	Banana
7	=MATCH("Cherrys",D1:D7,0) = #N/A	35	5	Strawberry	Apple
8	=MATCH(13,C1:C7,-1) = 5
9	=MATCH("Cherrys",E1:E7,-1) = 4
10	=MATCH(2,B1:B7,1) = #N/A
11	=MATCH(40,B1:B7,1) = 7
12	=MATCH(2,C1:C7,-1) = 7
13	=MATCH(40,C1:C7,-1) = #N/A
14	=MATCH(20,B1:B7,10) = 4
15	=MATCH(2,{1,2;3,4;5,6}) = #N/A
16	=MATCH("Cherry",D1:E2) = #N/A

1 - What is the position of the value 10 in the sorted block of cells.
2 - What is the position of the value 10 in the sorted block of cells. The default "match_type" is 1.
3 - What is the position of the item "Cherry" in the sorted block of cells.
4 - The default "match_type" is 1 so it returns the position of the largest value less than 13, which is 10 (position 2) (list in ascending order).
5 - The default "match_type" is 1 so it returns the position of the largest value less than "Cherrys" which is "Cherry" (position 3).
6 - The "match_type" is 0 and because the "lookup_value" does not exist, #N/A is returned.
7 - The "match_type" is 0 and because the "lookup_value" does not exist, #N/A is returned.
8 - The "match_type" is -1 so it returns the position of the smallest value greater than 13, which is 15 (position 5) (list in descending order).
9 - The "match_type" is -1 so it returns the position of the smallest value greater than "Cherrys" which is "Lemon" (position 4).
10 - The "match_type" is 1 and because the "lookup_value" is smaller than the first item, #N/A is returned.
11 - The "match_type is 1 and because the "lookup_value" is greater than the last item, 7 is returned.
12 - The "match_type is -1 and because the "lookup_value" is smaller than the last item, 7 is returned.
13 - The "match_type is -1 and because the "lookup_value" is greater than the first item, #N/A is returned.
14 - If the "match_type" > 1, then it is assumed to be 1.
15 - If "lookup_array" is a multi-dimensional array, then #N/A is returned.
16 - If "lookup_array" refers to multiple rows or columns, then #N/A is returned.