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.

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

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.