Lookup across multiple columns with lists in cells!!!

[sxhall]

Hi,

I have a range of data across three columns that contain numbers separated by commas. To left I have list of employee numbers that I need to reference back to when an instance of a number is found. To compound this i need to return all employee numbers when an instance of a number is found!!!

To hopefully make it easier to understand I have attempted an example...

Emp_______l__List_l__List__l_List__l__
Number____l__One_l__Two_l Three_l__

1112______l__1,2_ l_8_____l_7____ l__
1113______l__2___l_7_____l_6,5___ l__
1114______l__4___l_2_____l_8_____l__
1115______l__1,5_ l_9,10__l_11____l__


So for example if I typed 2 in to cell A1 in cell B1 it would return '1112,1113,1114' , a 7 would return '1112,1113' and 10 would only return '1115'

Not adverse to having another table in the spreadsheet to assist in getting the result as well. I have been at this for several hours now and just can't figure a way to get it work with additional tables and formulas combined together and am at the point of frustration!!!!

Thanks in advance for any help.

Steven

PS apologies for the crude example, but I don't know how to attach a proper example to a post.

 

[skorpionkz]

hmmm.... it always recalculating for me...

 

[sxhall]

hmmm.... it always recalculating for me...

Thought that would be the case, suspected it is the ageing equipment my company supplies!!!

I'll try on a few more machines here to see how they fare.

Thanks again for your help.