CODEWITHSUNDEEP
excelexcel-formula
West
West

Reputation: 2570

How to specify multiple ranges in excel filter formula

I would like to be able to use Excel's filter formula and get only specific columns as a result.

For example, I tried the below formula and failed.

=FILTER((A:B,D:D),A:A=3475,"")

How can I get this working? I want to get the filtered result where any value in column A equals 3475, and only get columns A,B and D

Upvotes: 1

Views: 9188

Answers (3)

Muhammad Nadeem
Muhammad Nadeem

Reputation: 1

=FILTER(INDEX(array,SEQUENCE(ROWS(array)),{1,2,4}),referance Column = required field)

Index: you can select your disere column from your array. like 1,2,4. if not required 3 so skip

Sequence+Rows: it will reffer your data till end (all rows of your array)

Include: here you can give your referance column from where you want to filter and witch word or field you are looking.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

You need use the proper array for the array argument to the filter function.

I used a Table since using whole-column references is inefficient.

For example, if you want to return only columns 1,2 and 4 of a table, you can use:

=INDEX(Table1,SEQUENCE(ROWS(Table1)),{1,2,4})

So your filter function might be:

=FILTER(INDEX(Table1,SEQUENCE(ROWS(Table1)),{1,2,4}),Table1[colA] = myVar)

enter image description here

IF, for some reason you don't want to use Tables, the following formula should also work:

=FILTER(INDEX($A:$D,SEQUENCE(LOOKUP(2,1/(LEN($A:$A)>0),ROW($A:$A))),{1,2,4}),myVar=INDEX($A:$A,SEQUENCE(LOOKUP(2,1/(LEN($A:$A)>0),ROW($A:$A)))))

as would, the less efficient:

=FILTER(INDEX($A:$D,SEQUENCE(ROWS($A:$A)),{1,2,4}),myVar=$A:$A)

Upvotes: 1

JvdV
JvdV

Reputation: 75840

You could use a single one formula like:

=TRANSPOSE(CHOOSE({1,2,3},FILTER(A:A,A:A=3475),TRANSPOSE(FILTER(B:B,A:A=3475)),TRANSPOSE(FILTER(D:D,A1:A4=3475))))

But considering performance, I'd go with two seperate formulas as proposed in the comments.

Upvotes: 1

Related Questions