使用多个匹配条件在excel中查找行号 [英] Find the ROW number in excel with multiple matching criteria

问题描述

我需要能够找到A1中的匹配条件等于或大于C列中的值并且小于或等于D列中的值的行的行号

I need to be able to find the row number of the row where matching criteria from A1 is equal or greater than values in column C and lesser or equal than values in column D

我可以使用INDEX和MATCH组合，但不确定是否应该将其用于多个条件匹配.

I can use INDEX and MATCH combo but not sure if this is something I should use for multiple criteria matching.

任何帮助或建议都将受到高度赞赏.

Any help or suggestions are highly appreciated.

推荐答案

由于您有多个条件，因此我不会使用MATCH来获取行号.一旦找到正确的行号，我仍然会使用INDEX来获取E中行的值.

I would not use MATCH to get the row number since you have multiple criteria. I would still use INDEX however to get the value of the row in E once the proper row number was discovered.

因此，我将使用一个数组公式，而不是MATCH，该数组公式使用包含多个条件的IF语句.现在请注意，需要使用ctrl + shift + enter输入数组公式. IF语句如下所示:

So instead of MATCH I would use an array formula using an IF statement that contained multiple criteria. Now note that array formulas need to be entered using ctrl + shift + enter. The IF statement would look like this:

=IF((A1>=C:C)*(A1<=D:D),ROW(A:A),"")

注意:我在这里没有使用AND公式，因为它不能接受数组.但是由于布尔值在Excel中只是1或0，因此将条件相乘就可以了.

Note: I did not use the AND formula here because that cannot take in arrays. But since booleans are just 1's or 0's in Excel, multiplying the criteria works just fine.

这现在为我们提供了一个仅包含空白和有效行号的数组.这样，如果第5行和第7行都有效，则数组将如下所示:

This now gives us an array containing only blanks and valid row numbers. Such that if rows 5 and 7 were both valid the array would look like:

{"","","","",5,"",7,"",...}

现在，如果我们用SMALL封装该IF语句，我们将获得所需的任何有效行.在这种情况下，由于我们只希望第一行有效，因此可以使用:

Now if we encapsulate that IF statement with a SMALL we can get whatever valid row we want. In this case since we just want the first valid row we can use:

=SMALL(IF((A1>=C:C)*(A1<=D:D),ROW(A:A),""),1)

如果第一个有效行为5，则将返回5.递增SMALL公式的K值将使您可以获取第二，第三等有效行.

Which if the first valid row is 5 then that will return 5. Incrementing the K value of the SMALL formula will allow you to grab the 2nd, 3rd, etc valid row.

现在当然，因为我们有了行号，所以简单的INDEX会为我们获得E列中的值:

Now of course since we have the row number a simple INDEX will get us the value in column E:

=INDEX(E:E,SMALL(IF((A1>=C:C)*(A1<=D:D),ROW(A:A),""),1))

这篇关于使用多个匹配条件在excel中查找行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！