答案1
默认情况下MATCH
函数假定你的数组按升序排序:
Match_type Behavior 1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. 0 MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order. -1 MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
为了获得正确的结果使用
MATCH(A5,$A$5:$A$293,0)
答案2
您使用的 Match 没有第三个参数。这将默认为 true,这意味着 Match 将返回近似查找,如果数据未排序,则很有可能产生错误结果。
如果你不知道正确或错误或0
论证1
,请阅读https://teylyn.com/2015/01/15/vlookup-why-do-i-need-true-or-false/其对 Vlookup 的适用方式与对 Match 的适用方式相同。