MATCH

說明

Use this function to search for a specified item in a range of cells and return the row number of that item in the range. Supported in Chains.

This function is useful for finding the row number of a particular value within a list or table.

MATCH(lookup_value, lookup_array, [match_type])

This function accepts the following arguments:

名稱	必要	說明	Valid input
`lookup_value`	是	The value you want to find in the lookup_array.	A cell reference, a cell range, a number, a text string, or a formula which results in any of these.
`lookup_array`	是	The range of cells being searched.	A range of cells containing possible lookup values.
`match_type`	沒有	Specifies how Excel matches the lookup_value with values in lookup_array.	1 (matches the largest value that is less than or equal to `lookup_value`) 0 (matches the first value exactly matching `lookup_value` -1 (matches the smallest value that is greater than or equal to `lookup_value`) Default is 1 if omitted.

公式	說明	結果
`=MATCH(30,A1:A4,0)`	This returns the row that contains the value 30 in the range A1:A4.	3
`=MATCH("Cherry",B1:B4,0)`	This returns the row that contains "Cherry" in the range B1:B4.	3
`=MATCH(25,A1:A4,1)`	This returns the row that contains the largest value less than or equal to 25 in A1:A4.	2
`=MATCH("D",B1:B4,1)`	This returns the row that contains the last value alphabetically less than or equal to "D" in B1:B4.	3
`=MATCH(15,A1:A4,-1)`	This returns the row that contains the smallest value greater than or equal to 15 in A1:A4.	2

MATCH returns the position of the found item as a number.
If match_type is 0, MATCH finds the first value exactly equal to lookup_value.
If the lookup_array is not sorted as expected, MATCH may return incorrect results without any error indication. This is because the function stops searching as soon as it finds a value that satisfies the condition, assuming that no better match will follow due to the expected sorting. If you're not sure about the sorting of your data, it's safer to use "0" (exact match) as the match_type, or to explicitly sort your data before using MATCH.
- If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value.The lookup_array must be in ascending order.
- If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value.The lookup_array must be in descending order.
If MATCH can't find a match, it returns the #N/A error.
MATCH is case-insensitive.

If you are not getting the results you expect, check that your lookup_array is correctly sorted for the specified match_type.
You can use MATCH with INDEX to create a powerful lookup formula that's more flexible than VLOOKUP. The page Using INDEX-MATCH to create flexible lookups provides an example.
You can combine MATCH with other functions such as MIN, MAX, or AVERAGE to find the position of specific values in a range.