Forum Discussion
Need help with formula for returning column headers
Hi guys
Please see example sheet - https://docs.google.com/spreadsheets/d/1-1LY18tN8InO2CuFNLskEPnV9cSmjolQDse5GSZsNkQ/edit?usp=sharing
I am trying to return the header (B2:F2) in I3 when a matching value for the array (B3:F11) is input into H3.
I have managed it kind of with this formula
=INDEX(B2:F2,MAX(IF(B3:F11=H3,COLUMN(B3:F11)))-COLUMN(A2)+1)
The issue is the array isn't a square. The different columns have different numbers of values to search (lots of blank cells in the array as a result of this). I would like it so that whenever I enter a value that isn't anywhere in the array it returns an error, or not found. Currently, if I enter a value that isn't anywhere in the array, it just always returns the first column header.
Also there are some columns that have the same value. I need the returned result to list each column header if the result is found in more than 1 column.
This one is a bit tricky.
Thanks in advance.
Regards
Jonathan
The formula I managed to get working is this - =IFERROR(TEXTJOIN(",",,FILTER(Sheet2!A1:L1,MMULT(SEQUENCE(,ROWS(Sheet2!A2:L61)),--(Sheet2!A2:L61=S9))))," Not Located")
2 Replies
=IFERROR(INDEX($B$1:$F$1,SMALL(IF(MMULT({1,1,1,1,1,1,1,1,1},--($B$3:$F$11=$H$3)),COLUMN($A:$E)),1)),"")&IFERROR(INDEX($B$1:$F$1,SMALL(IF(MMULT({1,1,1,1,1,1,1,1,1},--($B$3:$F$11=$H$3)),COLUMN($A:$E)),2)),"")&IFERROR(INDEX($B$1:$F$1,SMALL(IF(MMULT({1,1,1,1,1,1,1,1,1},--($B$3:$F$11=$H$3)),COLUMN($A:$E)),3)),"")&IFERROR(INDEX($B$1:$F$1,SMALL(IF(MMULT({1,1,1,1,1,1,1,1,1},--($B$3:$F$11=$H$3)),COLUMN($A:$E)),4)),"")&IFERROR(INDEX($B$1:$F$1,SMALL(IF(MMULT({1,1,1,1,1,1,1,1,1},--($B$3:$F$11=$H$3)),COLUMN($A:$E)),5)),"")Maybe with this formula which works in my spreadsheet. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.
The formula I managed to get working is this - =IFERROR(TEXTJOIN(",",,FILTER(Sheet2!A1:L1,MMULT(SEQUENCE(,ROWS(Sheet2!A2:L61)),--(Sheet2!A2:L61=S9))))," Not Located")
