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

 

[Aladin Akyurek]

Let's just pretend that you have 3 job titles.

Care to post a 10 row sample (input) and the output you want from this input given A1:A3 housing the criterion job titles?

 

[liampog]

Hi

Thanks for the reply.

Basically, out of a list of general staff names and positions, I'm trying to create a dynamic list of senior staff names.

Dynamic being that if someone's position is manually changed to a "senior" position, they will then appear in the dynamic list.

Hope this makes it clear.

Thanks
Liam



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Manager[/TD]
[TD]NAME[/TD]
[TD]POSITION[/TD]
[TD]OUTPUT[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Supervisor[/TD]
[TD]Andy A[/TD]
[TD]Supervisor[/TD]
[TD]Colin E[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Senior[/TD]
[TD]Bob B[/TD]
[TD]Attendant[/TD]
[TD]Hugh H[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Bob C[/TD]
[TD]Senior[/TD]
[TD]Liam E[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Colin E[/TD]
[TD]Manager[/TD]
[TD]Andy A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Colin M[/TD]
[TD]Attendant[/TD]
[TD]Gary H[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Dave D[/TD]
[TD]Senior[/TD]
[TD]Bob C[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]Ed G[/TD]
[TD]Attendant[/TD]
[TD]Dave D[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Fiona C[/TD]
[TD]Attendant[/TD]
[TD]Jack D[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Gary H[/TD]
[TD]Supervisor[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]Hugh H[/TD]
[TD]Manager[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]Ian D[/TD]
[TD]Attendant[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]Jack D[/TD]
[TD]Senior[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]Kevin G[/TD]
[TD]Attendant[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]Liam E[/TD]
[TD]Manager[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

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

=IFERROR(INDEX($B$2:$B$15,SMALL(IF(ISNUMBER(MATCH($C$2:$C$15,$A$1:$A$3,0)),ROW($B$2:$B$15)-ROW($B$2)+1),ROWS($E$2:E2))),"")

Alternatively, in E2 just enter and copy down:

=IFERROR(INDEX($B$2:$B$15,AGGREGATE(15,6,(ROW($B$2:$B$15)-ROW($B$2)+1)/ISNUMBER(MATCH($C$2:$C$15,$A$1:$A$3,0)),ROWS($E$2:E2))),"")

 

[liampog]

Hi Aladin

Thanks, that worked perfectly with a little tweaking!

Liam

 

[Aladin Akyurek]

Hi Aladin

Thanks, that worked perfectly with a little tweaking!

Liam

You are welcome. But what is the tweaking that is needed?

 

[liampog]

Hi Aladin

Oh nothing related to the formula... just the cell references, etc.

Liam

 

[Aladin Akyurek]

Hi Aladin

Oh nothing related to the formula... just the cell references, etc.

Liam

Right. Thanks for the update.

 

[evolve]

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

=IFERROR(INDEX($B$2:$B$15,SMALL(IF(ISNUMBER(MATCH($C$2:$C$15,$A$1:$A$3,0)),ROW($B$2:$B$15)-ROW($B$2)+1),ROWS($E$2:E2))),"")

Alternatively, in E2 just enter and copy down:

=IFERROR(INDEX($B$2:$B$15,AGGREGATE(15,6,(ROW($B$2:$B$15)-ROW($B$2)+1)/ISNUMBER(MATCH($C$2:$C$15,$A$1:$A$3,0)),ROWS($E$2:E2))),"")

Hi Aladin, hope you can help here.

I have a similar problem where the above formula can help, but after replicating the dataset above, the formula is returning "Bob C" for all rows in column E.
Can you please check?

I have large dataset where I need to extract unique values from one column (say column A), and it need to be based on multiple criteria from another column (column B).
So similar to above where the criteria in column A is to match the POSITION column (column C), and return NAME (column B). However in my case, the NAME column may have duplicates.

Thank you for your help in advanced.

 

[Aladin Akyurek]

@ evolve

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