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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2017[/TD]
[TD]2016[/TD]
[TD]2015[/TD]
[TD]2014[/TD]
[TD]2013[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]smith
[/TD]
[TD]jones[/TD]
[TD]henry[/TD]
[TD]dole[/TD]
[TD]hayes[/TD]
[TD]henry[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bush[/TD]
[TD]clinton[/TD]
[TD]harry[/TD]
[TD]mike[/TD]
[TD]mike[/TD]
[TD]clinton[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]donna[/TD]
[TD]brown[/TD]
[TD]brown[/TD]
[TD]sarles[/TD]
[TD]hayes[/TD]
[TD]miller[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]hayes[/TD]
[TD]zero[/TD]
[TD]bush[/TD]
[TD]jackie[/TD]
[TD]bill[/TD]
[TD]bill[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]dole[/TD]
[TD]mike[/TD]
[TD]james[/TD]
[TD]henry[/TD]
[TD]jones[/TD]
[TD]jones[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Smith
[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Bush[/TD]
[TD]2017, 2015[/TD]
[/TR]
[TR]
[TD]Donna[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Hayes[/TD]
[TD]2017, 2013[/TD]
[/TR]
[TR]
[TD]Dole[/TD]
[TD]2017, 2014[/TD]
[/TR]
[TR]
[TD]jones[/TD]
[TD]2016, 2013,2012[/TD]
[/TR]
[TR]
[TD]etc.......[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
smith	jones	henry	dole	hayes	henry
bush	clinton	harry	mike	mike	clinton
donna	brown	brown	sarles	hayes	miller
hayes	zero	bush	jackie	bill	bill
dole	mike	james	henry	jones	jones
Smith
Bush
Donna
Hayes
Dole
jones

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2012[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]2015, 2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]2013, 2013, 2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]2014, 2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]2012, 2013, 2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B9[/TH]
[TD="align: left"]{=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))))))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.