Display a list of columns if data is present within?

[ExcelHelpNecessary]

I have a table with the following data:

Rows: Names of People
Columns: Fruits

Is there anyway to set up a dropdown list with all the names, then in a listing display any columns next to their name with data inside?

ie: there are 6 columns (orange, apple, banana, grape, pear, melon) and Clarence has "yes" under the orange, banana and pear columns. How can I set up a dropdown so it shows all the names, and when you click Clarence it shows, in a list, like such:

Orange
Banana
Pear

Excel 2016 please

I've attached a sample workbook.

Thanks for any help!

[ExcelHelpNecessary]

Crossposted:

https://www.excelforum.com/excel-gen...ml#post5457508
https://old.reddit.com/r/excel/comme...ta_is_present/

[p45cal]

See if the attached works as you want…

Edit post posting, shucks, just seen you are Excel 2016. Hang on a bit. (Are macros out of the question?)

[p45cal]

See if this version gets you what you want (I think CONCAT was available in Excel 2016):

[ExcelHelpNecessary]

See if this version gets you what you want (I think CONCAT was available in Excel 2016):

Throwing a #NAME error, I don't think CONCAT is available. Are we able to use CONCATENATE? I tried subbing it in but it's not working correctly.

[p45cal]

For some reason, I'm not getting alerts to your replies, so sorry for the delay, I just came across this by chance.
I'm trying to find a solution by going back to Excel 2003; while I try, would a user-defined function (UDF) be acceptable? I can write one that duplicates the CONCAT function, so that when you ultimately upgrade to a version of Excel that includes CONCAT it should be fairly seamless to transfer as all you'd need to do is to delete the UDF. A UDF is a macro function so macros would have to be enabled.

ps. from all my exploration CONCAT is available in Excel 2016. Is your version of Excel really 2016, and is it as up to date as it can be?

[p45cal]

The attached workbook is Excel 2003.
Cell E3 has a similar formula as suggested earlier but uses a UDF version of CONCAT
Alternatively, using only built-in functions the cell E5 has another formula which uses helper cells G5:L5 whose formula is array-entered (using Ctrl+Shift+Enter rather then just Enter).

Being an Excel 2003 file there are no slicers so you you have to go to the dropown at cell A2 of Sheet2. You should be able to cobble something together from this in your more recent version of Excel.