Return a list of names if they match multiple criteria

D

Damo71

Board Regular
Joined
Aug 17, 2010
Messages
88
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
An attached table with some data and the expected result would make things clearer. What gender matches the criteria?
 
Upvote 0
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
 
Upvote 0
Possibly:

ABCDEFGHIJ
NamesGenderLanguageTalksDemonstrationsGenderMaleList
BennyMalePortugueseNoYesLanguageEnglishJohn
JohnMaleEnglishYesYesTalksYesHarold
BettyFemaleEnglishYesNoDemonstrationsN/A
HaroldMaleEnglishYesNo

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
Eric W said:
Possibly:

ABCDEFGHIJ
NamesGenderLanguageTalksDemonstrationsGenderMaleList
BennyMalePortugueseNoYesLanguageEnglishJohn
JohnMaleEnglishYesYesTalksYesHarold
BettyFemaleEnglishYesNoDemonstrationsN/A
HaroldMaleEnglishYesNo

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]J2[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.
Click to expand...
Thanks Both for your help. As my real list is much bigger, I've gone with Eric's solution. works like a charm.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Loaded question with filter function multiple criteria and return value.
Replies
2
Views
238
floW_5
F
E
  • Solved
Lookup value from range when name matches AND date falls within date range
Replies
4
Views
2K
Alex Blakenburg
A
GMC The Macro Man
  • Question Question
Count with Multiple Criteria - Follow up
Replies
2
Views
202
myall_blues
myall_blues
J
  • Solved
Match Data and Input Value
2
Replies
11
Views
2K
Fluff
Fluff
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
360
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,223,920
Messages
6,175,376
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top