in Google Sheets using formulars I would like to build an array of the columns that fulfills a simple criteria, so for example:

Then I would like to have the array {A:A,D:D,G:G} if I search for 'h1'

CodePudding user response：

Suppose your raw data is in a sheet named Sheet1.

In a new sheet (e.g., Sheet2), place the following formula in A1:

=IFERROR(FILTER(Sheet1!A:G,Sheet1!A1:G1="h1"))

You could also place this version of the formula in Sheet2 cell B1:

=IFERROR(FILTER(Sheet2!A:G,Sheet2!A1:G1=A1))

... and then enter "h1" (or some other text to match) into cell A1.

CodePudding user response：

I suppose what you want is to get the Column letter of the cell if some criteria is met. Like :"what colums have the right answer?" and the return that letter in some way.

So as far as i know sheets does not provide that as a simple function. (But im not very skilled in sheets).

Heres a hacky thing that might work for you: Somwhere in your sheet (eg bottom row # 100 make cells that hold your letters So A100 holds "A" or "A:A" B100 holds "B" or "B:B" and so forth.

then you can use filter function. If the row you want to want to investigate is the first: code: =FILTER(A100:Z100;A1:Z1="h2") that will give you your array.

Cheers Mads