Dynamic list based on multiple criteria

[liampog]

Hi

I want to create a dynamic list that looks up one of a range of criteria and creates a list.

There are 14 criterias in (for example), cells A1:A14. All of them uniquely different (they are job titles).

I have a list of 100 staff members with their names and job titles in columns C and D respectively. There are more than 14 job titles but I want a list to be created in Column B that just selects the 14 job titles in question.

Is this a good enough explanation of what I want to do?

I imagine it's going to be an array formula of some description.

Thanks
Liam

 

[evolve]

@ evolve

You want a unique result list in E because you can have duplicate names in B, right?

Yes, based on multiple criteria in column C, is that possible?

Thank you.

 

[Aladin Akyurek]

Yes, based on multiple criteria in column C, is that possible?

Thank you.

In D2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$15,SMALL(IF(FREQUENCY(IF($B$2:$B$15<>"",IF(ISNUMBER(MATCH($C$2:$C$15,$A$1:$A$3,0)),MATCH($B$2:$B$15,$B$2:$B$15,0))),ROW($B$2:$B$15)-ROW($B$2)+1),ROW($B$2:$B$15)-ROW($B$2)+1),ROWS($D$2:D2))),"")

 

[evolve]

In D2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$15,SMALL(IF(FREQUENCY(IF($B$2:$B$15<>"",IF(ISNUMBER(MATCH($C$2:$C$15,$A$1:$A$3,0)),MATCH($B$2:$B$15,$B$2:$B$15,0))),ROW($B$2:$B$15)-ROW($B$2)+1),ROW($B$2:$B$15)-ROW($B$2)+1),ROWS($D$2:D2))),"")

Brilliant, thank you very much!!

 

[Aladin Akyurek]

Brilliant, thank you very much!!

You are welcome.