Excel Filter two dimension array

Question

Is there a way I can use FILTER to filter an array of ef 3 X 5 to just cells containing a given value;

I've got as far as two filter functions,

But i have to where the values are re row / column I was hopiing to do somthin as below but using filter / index/match ?

is therer a way to use { } in filter as with index etc. INDEX(G9:G12,{2;3},1)

Any Ideas ?

Richard.

Answer 1

Hi,

You may already be 3/4 of the way there:

=TEXTSPLIT(TEXTJOIN(",",TRUE,IF(--((A1:E5=G1)+(A1:E5=H1)),A1:E5,"")),",")

If you want the result to list vertically:

=TOCOL(TEXTSPLIT(TEXTJOIN(",",TRUE,IF(--((A1:E5=G1)+(A1:E5=H1)),A1:E5,"")),","))

Answer 2

Hi. I see you have a solution, so I won't go into it.

Just to mention, I would not filter on :

(LEFT(data)="a")*((RIGHT(data)="1")+(RIGHT(data)="3")))

but rather:

"c" & {1,3}

A Python function would not use filter, but something else (Same concept thou)

Answer 3

You can use this template:

=LET(data, TOROW(A1:E5), FILTER(data, condition))

For example:

=LET(data, TOROW(A1:E5), FILTER(data, (LEFT(data)="a")*((RIGHT(data)="1")+(RIGHT(data)="3"))))

Answer 4

ideally I would like to get , in the above a 2 X 1 result rather than the positions in

the original

I did think about

    --(A4:D8= "a")\* SEQUENCE( ROWS(A4:A8)) 

to give positions or row and columns, but this is only for one match and it did not work

this is the closest Ive got;

INDEX(A4:D8,SUMPRODUCT(--(A4:D8="a")*SEQUENCE(ROWS(A4:A8))),SUMPRODUCT(--(A4:D8="a")*SEQUENCE(ROWS(A4:A8))))

but as this is just for one value.

RD

Answer 5

What should happen if the cells meeting the criteria are not in a single row or column, but spread through the range?

What should be the shape of the output in that case?