Looking up a number and returns all people with that number

[Dhanesh Nair]

Hi There
Have a query,

I have a presentation to management.
There are 20 people with different skills ( each skill being given a number 1-40).
The first column has the name and the second column has the skills in one cell with spaces separating the skills numbers. as per attached.
Is there a way a look up could be structured wherein I input number 4 and it brings up all the people with that skill ( #4)
I have attached a copy of the sheet I am working on.
Please let me know if you need any clarification.
I have tried a few things but somehow...the skills being in one cell is causing a problem.

Thanks in advance.

 

[Chihiro]

Did you upload the right file? I couldn't match what you describe to the sheet.

But from what you describe I'd suggest converting your data to Table format and using filter to bring up list of people that match your criteria.

 

[Dhanesh Nair]

Hi Chihiro

Thank you fr your reply.

Yes it is the right file.

Column A contains the names of people A & B
Column D contains the skills with their ref numbers.
Each skill has been given a specific number.
Such as 2- Pastoral care.
4 - Administration.
A and B both have skills 2, 4, 6 and so on.

I am looking for a solution where if I entre the skill number in any cell, the cell adjacent returns all the names of people that have that skill.

Please let me know if this is doable

THanks Chihiro
Nice day!

 

[Chihiro]

Please upload file with more than just one row of data.

 

[Dhanesh Nair]

Hi Chihiro
Please find attached the file.
Tab 1 includes names of employees with their specific skills ( each with a specif number as per index on Tab2 )
I am after a query where if I key in a specific skill number all the employees with that skill come up.
Ex : if I key in # 2: the people with Pastoral care come up.

Could you please help me here.

Thanks Chihiro

 

[Chihiro]

See attached.

Created Named Range for Skill list called lstSkill =Skill!$A$2:$A$27
Data validation list in Sheet3!B2 using above Named Range.

Used following formula in Sheet3!B6 and copied down.
=IFERROR(INDEX(Data!$A$2:$A$16,SMALL(IF(ISNUMBER(SEARCH("*"&$B$2&"*",Data!$B$2:$B$16,1)),ROW(Data!$A$2:$A$16)-1,""),ROW(1:1)),0),"")

Formula must be entered as array (CSE).

P.S. Couldn't use just numbers as you will have multiple matches to string (ex. 1. & 31. will both return positive for partial match).

 

[Dhanesh Nair]

Hi Chihiro

Hope you are well.

Thank you so much for this...sorry was away for a few days.

Appreciate your help in resolving this .

Thanks again

 

[Chihiro]

You are welcome