Forum Discussion
Kate Jack
Nov 14, 2018Copper Contributor
Push in the right direction, pull multiple cells of data through lookup
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
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-into-one-cell/
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-into-one-cell/
- Kate JackCopper Contributor
Fantastic thank you Sergei- that link gave me everything I needed.
Thanks again
Kate