1

My question is very similar to the one described here: How can I pass an array's elements as individual arguments to a function? The twist here is doing it inside Google sheets. In column A, there are a series of entries, from which I want to filter those that match certain criteria, and then in some other individual cells, populate the n-th result of the filter.

COL A1:A4

John A

Hector C

Mario G

Hecate J

Cell C4: =CHOOSE(1,FILTER(A1:A4, LEFT(A1:A4,1)="H"))

Cell D8: =CHOOSE(2,FILTER(A1:A4, LEFT(A1:A4,1)="H"))

But what happens is that C4:C5 are populated with Hector C and Hecate J, and D8 returns "Error Function CHOOSE parameter 1 value is 2. Valid values are between 1 and 1 inclusive."

My conclusion is that the Array that FILTER returns, is simply taken as a single argument by the CHOOSE function, instead of taking the individual elements as arguments. I tried fiddling with the ArrayFormula, but no luck. I tried the long shot of preppending the "..." and obviously is not recognized as a function.

Any ideas that do not involve writing my own Script function? Thanks.

player0
  • 124,011
  • 12
  • 67
  • 124
iatorresc
  • 33
  • 5

1 Answers1

1

You can use index() instead:

Then you can select the n-th element of the list like that:

=index(FILTER(A1:A4, LEFT(A1:A4,1)="H"),2)

Instead of having 2 as a hardcopy number you can also specify the length of the list to take for example the last element of the filtered list:

=index(FILTER(A1:A4, LEFT(A1:A4,1)="H"),COUNTA(FILTER(A1:A4, LEFT(A1:A4,1)="H")))
Marios
  • 26,333
  • 8
  • 32
  • 52