Return a list of names if they match multiple criteria

[Damo71]

Hi

Row 1 has my headers. Starting in A1 and working to the right, we have "Names", "Gender", "Language", "Talks", "Demonstrations" respectively.

Suppose I have a list of 100 names in column A, then column B will be either male or female, Column C will be English or Portuguese, Columns D & E will have either a Yes or No.

Each range of info (rows 2-101) will be named ranges according to the header.

So far so good.

Let's say that in Column G1:G4 I have "Gender", "Language", "Talks", "Demonstrations" respectively. and in H1:H4 The I have validation lists to allow the user to select his options.

I want in Column J (say 2 to 101) to put formulae that will return only the names that match the criteria the user has input in H1:H4, without leaving gaps for the people whose names don't match.

Example: A2 = Benny, A3 = John, A4= Betty, A5 = Harold,

So lets say Benny speaks Portuguese, is male and can do demonstrations
John is English and can do talks and demonstrations
Betty is English and can only do talks
Harold is English and can only do talks


So if the user selects "Male", "English" and "Demonstrations", The formula in J2 will return "John", because he is the first person in the list that matches all of my criteria. J3-J102 would be blank in my example as none of the other students match the criteria.

If the user selects "Male", "English" and "Talks", The formula in J2 will return "John", and J3 would return Harold because he also matched the criteria.

How do I do this? What formula would I need in J2 (which I could copy down)?

I appreciate it could be done with a pivot table. but that is not an option for my in this instance.

Many thanks

 

[István Hirsch]

An attached table with some data and the expected result would make things clearer. What gender matches the criteria?

 

[hennahairgel]

I'd create a helper column first to show or hide the name if it matches the criteria e.g. in column F add the following code:
=IF(AND(B2=$H$1,C2=$H$2,D2=$H$3,E2=$H$4),A2,"")

And then in column J2 put:
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(F:F,SMALL(IF($F$2:$F$101<>"",ROW($F$2:$F$101)),ROWS($J$2:J2)))))
And use Ctrl-Shift-Enter to create a formula array

 

[Eric W]

Possibly:

	A	B	C	D	E	F	G	H	I	J
1	Names	Gender	Language	Talks	Demonstrations		Gender	Male		List
2	Benny	Male	Portuguese	No	Yes		Language	English		John
3	John	Male	English	Yes	Yes		Talks	Yes		Harold
4	Betty	Female	English	Yes	No		Demonstrations	N/A
5	Harold	Male	English	Yes	No
6
7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula J2 {=IFERROR(INDEX($A$2:$A$101,SMALL(IF(($B$2:$B$101=$H$1)+($H$1="N/A"),IF(($C$2:$C$101=$H$2)+($H$2="N/A"),IF(($D$2:$D$101=$H$3)+($H$3="N/A"),IF(($E$2:$E$101=$H$4)+($H$4="N/A"),ROW($A$2:$A$101)-ROW($A$2)+1)))),ROWS($J$2:$J2))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

As you can see, I have N/A in H4. I'd recommend in your drop-down lists in H1:H4 that you include N/A as an option. This will allow you to make selections with less than 4 criteria. The formula I provided here will allow that.

 

[Damo71]

Possibly:

	A	B	C	D	E	F	G	H	I	J
1	Names	Gender	Language	Talks	Demonstrations		Gender	Male		List
2	Benny	Male	Portuguese	No	Yes		Language	English		John
3	John	Male	English	Yes	Yes		Talks	Yes		Harold
4	Betty	Female	English	Yes	No		Demonstrations	N/A
5	Harold	Male	English	Yes	No
6
7

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula J2 {=IFERROR(INDEX($A$2:$A$101,SMALL(IF(($B$2:$B$101=$H$1)+($H$1="N/A"),IF(($C$2:$C$101=$H$2)+($H$2="N/A"),IF(($D$2:$D$101=$H$3)+($H$3="N/A"),IF(($E$2:$E$101=$H$4)+($H$4="N/A"),ROW($A$2:$A$101)-ROW($A$2)+1)))),ROWS($J$2:$J2))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

As you can see, I have N/A in H4. I'd recommend in your drop-down lists in H1:H4 that you include N/A as an option. This will allow you to make selections with less than 4 criteria. The formula I provided here will allow that.

Thanks Both for your help. As my real list is much bigger, I've gone with Eric's solution. works like a charm.

 

[Eric W]

Glad we could help.