索引匹配唯一值与多个标准?

I have the following list of users on 1 sheet, along with their age and sports interest:

我在一页纸上有以下用户列表，以及他们的年龄和体育兴趣:

A       18        Football
B       19        Rugby
C       18        Football
D       50        Dance
D       21        Rugby
A       25        Football

I then have an index match array formula on sheet 2 which is looking up my list of users and outputting them on sheet 1.

然后，我在表2上有一个索引匹配数组公式，它查找我的用户列表，并将它们输出到表1。

Formula:

公式:

 =IFERROR(INDEX('Activity Data'!$A$2:$A$5000, MATCH(0, COUNTIF($B$36:B36,'Activity Data'!$A$2:$A$5000), 0)),"")

This gives me a unique list of users like so:

这给了我一个独特的用户列表:

A
B
C
D

However, i only want to look up those users who are 18 years old and who are interested in football.

但是，我只想查找那些18岁，对足球感兴趣的用户。

How can i add multiple match criteria to this formula in order to get the desired result?

如何在这个公式中添加多个匹配条件以得到期望的结果?

Thanks in advance

谢谢提前

1 个解决方案

#1

If you do want to persevere with a formula, you can force a '1' when the criteria are not fulfilled as follows:-

如果你确实想坚持一个公式，你可以强制一个“1”当标准不满足如下:-

=IFERROR(INDEX('Activity Data'!$A$2:$A$10, MATCH(0, SIGN(COUNTIF($C$36:C36,'Activity Data'!$A$2:$A$10)+('Activity Data'!$C$2:$C$10<>"Football")+('Activity Data'!$B$2:$B$10<>18)), 0)),"")

(I am only testing it on 10 rows but should work for any range)

(我只对10行进行测试，但应该适用于任何范围)

Actually realised SIGN isn't necessary because anything not equal to zero won't be matched so:-

实际上意识到符号是不必要的，因为任何不等于零的东西都是不匹配的

=IFERROR(INDEX('Activity Data'!$A$2:$A$10, MATCH(0, COUNTIF($C$36:C36,'Activity Data'!$A$2:$A$10)+('Activity Data'!$C$2:$C$10<>"Football")+('Activity Data'!$B$2:$B$10<>18), 0)),"")

But you may prefer to invert the logic and solve it in the more normal way like this:-

但是你可能更喜欢颠倒逻辑，用更普通的方式来解决:-

=IFERROR(INDEX('Activity Data'!$A$2:$A$10, MATCH(1, (COUNTIF($C$36:C36,'Activity Data'!$A$2:$A$10)=0)*('Activity Data'!$C$2:$C$10="Football")*('Activity Data'!$B$2:$B$10=18), 0)),"")

These are array formulae and must be entered using CtrlShiftEnter

这些是数组公式，必须使用ctrl - shiftenter输入

#1