Forum Discussion

blastlast's avatar
blastlast
Copper Contributor
Feb 09, 2020

Combining multiple ranges with filter-formula?

Hi!   In Google Spreadsheets you can combine multiple ranges with the filter function with the use of { } as shown below. Is there any way of doing this with the new Filter-formula in Excel?   Th...
excel
Formulas and Functions
blastlast's avatar
blastlast
Copper Contributor
Feb 13, 2020

PeterBartholomew1 

 

I'm not sure if I understand what you mean? In Google Spreadsheets it is possible and it is a great feature to be able to make advanced filters of multiple ranges or sheets.

 

But as I understand, it's not possible in Excel, only Google Sheet.

 

Thanks!

Accountplish's avatar
Accountplish
Copper Contributor
Feb 10, 2022

blastlast While the method is not the same as Google Sheets, the result is possible in Excel! https://youtu.be/Onudkw9DMlU?t=240 best explains but essentially you'll want to have an original filter function with the full range then wrap the original filter function with a new filter function that has an {array} for the "include" parameter with 1s for the number column(s) you do want and 0s for the number column(s) you do not want.

 

For example, say I wanted to a range (A to D) where D = "Microsoft" but only wanted to select columns A and C, I'd insert in the formula

 

=FILTER(FILTER(A2:D10,D2:D10="Microsoft"),{1,0,1,0})

 

Hope this helps!

Resources