search array and return one or more column headings as results

[excelhunter]

Hello. I am a first time user. I can usually search for what I need and adapt the solution to my situation but this challenge has me wanting. I have a set of data that I would like to search as a set of columns or an array and I would like to return the results of each occurrence to a cell or if necessary a set of cells in a row extending to the right. My data set looks like the following.

	a	b	c	d	e	f
1	2017	2016	2015	2014	2013	2012
2	smith	jones	henry	dole	hayes	henry
3	bush	clinton	harry	mike	mike	clinton
4	donna	brown	brown	sarles	hayes	miller
5	hayes	zero	bush	jackie	bill	bill
6	dole	mike	james	henry	jones	jones

<tbody>
</tbody>

I have a column of data that shows the entire roster of names (80+) that I am using as the search string and I would like to create a formula that looks up the array similar to the one above and returns the year in the column header that corresponds with the name occurrence. The results should look like below. The names in the left column are the search string and the results would be the years in the second column. I figure once I have the first formula I can just copy it down the column and use relative referencing to have it reference each successive name as a search string in the data array.

Smith	2017
Bush	2017, 2015
Donna	2017
Hayes	2017, 2013
Dole	2017, 2014
jones	2016, 2013,2012
etc.......

<tbody>
</tbody>

If it is difficult to return multiple results to a cell, then the rresults may extend to cells to the right. Any help is very much appreciated. I have tried combinations of lookup, match, index, etc without results.
Regards,

 

[DRSteele]

Welcome to the forum.

So far I've been able to come up with a partial solution for you. I cannot figure out how to manipulate the arrays to put things in descending order nor how to eliminate the duplicates (eg. my Hayes has 2013 twice). You will need Excel2016 to use this algorithm because function TEXTJOIN works with 2016 onwards only. I hope this helps.

	A	B	C	D	E	F
1	2017	2016	2015	2014	2013	2012
2	smith	jones	henry	dole	hayes	henry
3	bush	clinton	harry	mike	mike	clinton
4	donna	brown	brown	sarles	hayes	miller
5	hayes	zero	bush	jackie	bill	bill
6	dole	mike	james	henry	jones	jones
7
8
9	Smith	2017
10	Bush	2015, 2017
11	Donna	2017
12	Hayes	2013, 2013, 2017
13	Dole	2014, 2017
14	jones	2012, 2013, 2016

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11​

Array Formulas Cell Formula B9 {=TEXTJOIN(", ",1,INDEX($A$1:$F$1,,N(IF(1,AGGREGATE(14,6,COLUMN($A$1:$F$6)*(A9=$A$2:$F$6),ROW(INDIRECT("1:"&COUNTIFS($A$2:$F$6,A9))))))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

 

[excelhunter]

Thank you! Unfortunately I forgot to include that I am running Excel 2011 on a Mac. I could upgrade but I'm not sure any of my partners would be able to use the spreadsheet then. I've seen a couple of visual basic user defined functions that are close. I'm wondering if I will need to go that route. I really appreciate it.

 

[DRSteele]

I see. I won't be able to help you with VBA...sorry. Best of luck.