Geek Answers Handbook

Does the index match unique values ​​with multiple criteria?

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

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

Then I have an index matching formula on sheet 2 that looks at a list of my users and displays them on sheet 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, for example:

A
B
C
D

However, I want to see only those users who are over 18 years old and who are interested in football.

How can I add several matching criteria to this formula to get the desired result?

Thanks in advance

+4
source share
1 answer

, "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)),"")

( 10 , )

SIGN , : -

=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)),"")

enter image description here

: -

=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)),"")

Ctrl Shift Enter

+1

More articles:


All Articles