I'm trying to return the header of every column in which a value appears, and put these headers in one cell separated by commas e.g. if "xyz" is contained within columns titled Column1 and Column7, then this formula would return "Column1, Column7" in the cell.

• The headers of the column are in Row H4:AF4

• The data is underneath, in H5:AF99

• The value I'm looking up sits in cell E1

At the moment this formula is working for some values, but not all:

=JOIN(", ",QUERY(TRANSPOSE(H4:AL99),"Select Col1 where "&JOIN(" or ",ARRAYFORMULA("Col"&(COLUMN(H4:AL4)-COLUMN(H4)+1)&" = '"&$E$1&"'"))))

Can anyone explain why this formula works for some of my lookup values but not all?

edit: I have since discovered this formula is working fine for the first 30 values in my lists but does not return any value past this and instead returns a #N/A. Any ideas why?

I'm trying to return the header of every column in which a value appears, and put these headers in one cell separated by commas e.g. if "xyz" is contained within columns titled Column1 and Column7, then this formula would return "Column1, Column7" in the cell.

• The headers of the column are in Row H4:AF4

• The data is underneath, in H5:AF99

• The value I'm looking up sits in cell E1

At the moment this formula is working for some values, but not all:

=JOIN(", ",QUERY(TRANSPOSE(H4:AL99),"Select Col1 where "&JOIN(" or ",ARRAYFORMULA("Col"&(COLUMN(H4:AL4)-COLUMN(H4)+1)&" = '"&$E$1&"'"))))

Can anyone explain why this formula works for some of my lookup values but not all?

edit: I have since discovered this formula is working fine for the first 30 values in my lists but does not return any value past this and instead returns a #N/A. Any ideas why?

See spreadsheet here: https://docs.google.com/spreadsheets/d/1bF7e8kZTo7HLeZWI0EefdHJgCbyoKrU-QKoPOfnTH2o/edit?usp=sharing

I'm trying to return the header of every column in which a value appears, and put these headers in one cell separated by commas e.g. if "xyz" is contained within columns titled Column1 and Column7, then this formula would return "Column1, Column7" in the cell.

• The headers of the column are in Row H4:AF4

• The data is underneath, in H5:AF99

• The value I'm looking up sits in cell E1

At the moment this formula is working for some values, but not all:

=JOIN(", ",QUERY(TRANSPOSE(H4:AL99),"Select Col1 where "&JOIN(" or ",ARRAYFORMULA("Col"&(COLUMN(H4:AL4)-COLUMN(H4)+1)&" = '"&$E$1&"'"))))

Can anyone explain why this formula works for some of my lookup values but not all?

I'm trying to return the header of every column in which a value appears, and put these headers in one cell separated by commas e.g. if "xyz" is contained within columns titled Column1 and Column7, then this formula would return "Column1, Column7" in the cell.

• The headers of the column are in Row H4:AF4

• The data is underneath, in H5:AF99

• The value I'm looking up sits in cell E1

At the moment this formula is working for some values, but not all:

=JOIN(", ",QUERY(TRANSPOSE(H4:AL99),"Select Col1 where "&JOIN(" or ",ARRAYFORMULA("Col"&(COLUMN(H4:AL4)-COLUMN(H4)+1)&" = '"&$E$1&"'"))))

Can anyone explain why this formula works for some of my lookup values but not all?

edit: I have since discovered this formula is working fine for the first 30 values in my lists but does not return any value past this and instead returns a #N/A. Any ideas why?