Displaying single instance Name with additional criteria - INDEX & MATCH Function

1
votes

I have a set of multiple instances of names in column E and a respective number assigned to each name in column D. I have used Formula:

=IFERROR(INDEX($E$4:$E$12;MATCH(0;COUNTIF($H$3:H3;$E$4:$E$12);0);1);"")

in H4:H8 which works well for the extraction of single instance of the names from E4:E12.

enter image description here

Now I want to add one more criteria in the formula, to extract only names whose number from D4:D12 =4 and in the above example, the result would be only d instead of a,b,c,d,e.

I tried with COUNTIFS by adding additional criteria like:

=IFERROR(INDEX($E$4:$E$12;MATCH(0;COUNTIFS($H$3:H3;$E$4:$E$12;$D$4:$E$12;$D$4:$D$12="4");0););"")

But nothing is displayed this time.

Any Idea, how this criteria should be added?

1
excelindexingexcel-formulaoffice365match
What version of Excel is it you are using? Also, why just d and not also b? - JvdV
I am using office 365. only d because all instances of d have a number "4", whereas b has "3" and "4". - Ushay

1 Answers

0
votes

You can try the following with Microsoft365:

enter image description here

Formula in H4:

=UNIQUE(FILTER(E4:E12,COUNTIFS(E4:E12,E4:E12,D4:D12,"<>4")=0,""))

Note: Change the commas to semicolon for your locale if needed.