Push in the right direction, pull multiple cells of data through lookup

[Kate Jack]

Hello,

I'm hoping someone might be able to help with my fairly basic excel competency.

I have a table of data pulled from one of our systems that lists the names of courses along the top row of the table, with a list of staff in the first column. In some of the cells there is a number '1' to indicate that the course listed in the top row is outstanding for this employee.

Where an employee does have a "1", I need to pull the title of the course from that top row, for each cell along the row that states "1"- which could be numerous.

I've tried using hlookups but it only seems to pull the title of one course even if the employee has multiple "1" indicators.

Is there a way to achieve this?

Many thanks

[#2 · Accepted solution]

Hi Kate,

If you are an Office365 subscriber you may use TEXTJOIN

=TEXTJOIN(",",TRUE,IF(C3:H3=1,$C$2:$H$2,""))

That is an array formula.

Otherwise that could be UDF, please check for example https://www.get-digital-help.com/2010/12/20/excel-udf-lookup-and-return-multiple-values-concatenated...

[#3]

Fantastic thank you Sergei- that link gave me everything I needed.

Thanks again

Kate