Get Column Headers where value exists, Excel

Question

I am trying to create a column that concatenates the headings from other columns, based on the location of a value within the range of data under the headings using an excel formula. My table looks like this:

2014 | 2015 | 2016 | 2017
-------------------------
1234   4567   6547   1234
2583   3698   4567   3214
9874   1234   6664   5896

Now, this table has an extensive list of rows with values.

I want to create a cell that a user can type a number in and it will return the row heading(s) in the cell adjacent to it. if the number occurs in more than one column, than concatenate the headings using a comma separator. i.e.

Enter Number:    Years Occurred 
    1234         2014,2015,2017

Rules: Each column has a list of unique numbers. No number occurs in a column more than once.

The best example I can find is here, where the formula returns the heading for the max value of the range of data:

https://www.extendoffice.com/documents/excel/2547-excel-find-highest-value-in-a-row-and-return-column-title.html

Thanks

Answer 1

Use TEXTJOIN as an Array Formula:

=TEXTJOIN(",",TRUE,INDEX(A1:D1,N(IF(1,MODE.MULT(AGGREGATE(15,6,COLUMN(A2:D4)/(A2:D4=F2),ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,COUNTIF(A2:D4,F2))))*{1,1})))))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter, instead of Enter when exiting edit mode. If done correctly Excel will put {} around the formula.

Note: TEXTJOIN is available with Office 365 Excel and not earlier versions.

---

If you do not have Office 365 Excel then you will need some helper columns.

In the first Helper cell put:

 =IF(ISNUMBER(MATCH($F$4,A:A,0)),"," & A1,"")

Then copy over the same number of columns as your data.

Then use this to concatenate:

=MID(H4&I4&J4&K4,2,999)

Adding cells for more columns.