0

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

2
  • The most efficient way to get help is to show rather than tell by sharing a link to your sheet (or a copy of it). This provides the volunteer contributors here a starting point. Otherwise, you'd be expecting volunteers to create their own sheet and manually enter test data themselves before they could even begin. And even at that, it doesn't account for aspects of your real data and layout that might affect a solution. Just be sure, if you choose to share such a link, that you set the permissions (when creating the link) to "Anyone with the link..." and "Editor."
    – Erik Tyler
    Jun 12 at 12:47
  • Ok sure I will look to upload something. I have realised the formula I have is working fine - but only to a maximum of 30 values in the list. After 30 values it fails to return the value of the list it's in and instead returns a #N/A.
    – gilbert
    Jun 15 at 2:22
0

You want to display the Column references for a given search value in any column(s) within a given range. Values in the range are strings and any Row in any Column may be blank - that is, the data is not contiguous. The value of the column header indicates the relative column number within the data range.

PARAMETERS
Refer to the image and enter the values for:

  • first Column of data (alpha value),
  • last Column of data (alpha value),
  • first Row of data (the header row),
  • last Row of data.
  • All ranges in the formula are automatically calculated based on these four values.

FORMULA
The formula is:

=join(", ",QUERY({TRANSPOSE(indirect(C3&C5&":"&C4&C6))},"Select Col1 where "&JOIN(" or ",ARRAYFORMULA("Col"&(COLUMN(indirect((C3&C5+1)&":"&address(C5+1,C6+C5,4,true)))-COLUMN(indirect(C3&5))+1)&" = '"&$B$2&"'"))))


LOGIC

Query Data Range
TRANSPOSE(indirect(C3&C5&":"&C6&C7))

  • the data range (H4:AF99) is built from the parameters in cells C3, C4, C5 and C6 (refer snapshot)
  • the data is inserted using INDIRECT
  • the data is transposed to that "Col1" contains the headers(the List#)

Where clause
where "&JOIN(" or ",ARRAYFORMULA("Col"&(COLUMN(indirect((C3&C5+1)&":"&address(C5+1,C6+C5,4,true)))-COLUMN(indirect(C3&5))+1)&" = '"&$B$2&"'"

  • The purpose of this clause is to create a where condition like " where Col2 = 'ffff' or Col3 = 'ffff' or... Col96 = 'ffff'"
  • Although there are only 25 Columns (H to AF) in the physical data, there are 96 Rows of data (including the header row).
  • When the data is transposed (and rows become columns, and vice versa), there are 96 columns of data. "List#" occupies Col1, and data is in columns 2 to 96.
  • Relative column numbers are generated by this snippet:
    • COLUMN(indirect((C3&C5+1)&":"&address(C5+1,C6+C5,4,true)))-COLUMN(indirect(C3&5))+1
    • which resolves to something like:
    • Column(H5:CY5)-Column(H5)-1
  • when the JOIN function is added the outcome looks like this:
    • " where Col2 = 'ffff' or Col3 = 'ffff' or... Col96 = 'ffff'"
    • note the LAST column is #96

SAMPLE

  • The data range in the sample is H4:AF99. Some columns and rows in the snapshot have been hidden
  • The answer includes a value of "List25" because there is a value of "ffff" in Column 25 at row 99 (AF99).
    • The purpose of this cell value is to confirm that the formula evaluates every cell in the data range.
    • Other than the header, it is the only value in Column AF.

enter image description here

7
  • Thanks very much for the reply
    – gilbert
    Jul 20 at 2:02
  • This doesn't quite work for me - i've uploaded a sheet and you will see what I mean. docs.google.com/spreadsheets/d/…
    – gilbert
    Jul 20 at 2:06
  • @gilbert I've requested access, but it would be better if you were to also describe (in words) what aspect doesn't meet your goal. We would describe this as a "successful outcome"; you could/should include this in your spreadsheet, and a snapshot (at least) in your question.
    – Tedinoz
    Jul 21 at 0:19
  • Apologies - made public. The lookup only works to 30 rows and no further down.
    – gilbert
    Jul 21 at 6:25
  • okay. The problem isn't that it only processes the "first 30 rows." Look at your Column 15-there are blank cells between Cell O9 ("nn") and Cell O29 ("ffff"). Your question requires an additional parameter - "in any given Column, there may be blank cells between data values" (or words to that effect). So, the answer needs to take these blank cells into account.
    – Tedinoz
    Jul 22 at 5:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.