Is there an Excel function that works the same as filter() from Google Sheets?

2

I have a list of names that each have a value (C:C, D:D). I want to generate a new column (A:A) that only shows the names that have the values I want, without any spaces.

In Google Sheets it's trivial:

Spreadsheet Screenshot

I'm moving to Excel due to some shortcomings with Sheets that I can't get around, and can't figure out how to duplicate this functionality. The closest I've come is with MATCH/INDEX, but I was never able to get there. All of the research I've done has pointed to needing VB, which is something I'd like to avoid.

JoeFish

Posted 2018-01-17T17:15:42.167

Reputation: 121

Shortcomings with Sheets? I'm a long term google sheets user who now has to work with Excel online for a particular customer. And I find Excel online very limiting compared to Google Sheets in terms of functionality and speed. – Chris – 2020-01-29T11:26:57.117

Answers

3

In Excel, one must put a formula in each cell that one is expecting a result. So, the short answer is no there is no function in Excel that would exactly match the use of Filter in Sheets.

To do this I like to use INDEX with AGGREGATE:

=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($D$1:INDEX(D:D,MATCH("zzz",C:C)))/($D$1:INDEX(D:D,MATCH("zzz",C:C))<>0),ROW(1:1))),"")

Put that in A1 and copy down till you get blanks. As stated one must put a function in each cell that one expects a return.

enter image description here

Scott Craner

Posted 2018-01-17T17:15:42.167

Reputation: 16 128

Asked: 2018-01-17T17:15:42.167

Viewed: 7 700 times

Active: 2018-01-17T17:23:12.427