Nov 14 2018 03:06 AM
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
Nov 14 2018 03:22 AM
SolutionHi 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...
Nov 14 2018 08:16 AM
Fantastic thank you Sergei- that link gave me everything I needed.
Thanks again
Kate
Nov 14 2018 03:22 AM
SolutionHi 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...