5

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.

1
  • 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
    Jan 29 '20 at 11:26
5

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

2

Just to update this question, =FILTER now works in Office 365 versions of Excel along with some other dynamic formulas. Excel 2019 or earlier doesn't support this function and I don't think there's any plans to add the functionality into these older products.

2
  • Which versions exactly?
    – Ramhound
    Jan 27 at 23:09
  • apologies - I have added more detail
    – 5Diraptor
    Feb 2 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.