Required filter non blank values by formula.

[Juniad]

Hi,
I have 5 columns where i have value X & Y and some blank cells as well. i need to filter non blank values and return the result column B to D..

 

[bosco_yip]

Try,

In E3, copied across and down :

=IFERROR(INDEX(B$2:B$27,AGGREGATE(15,6,ROW($A$2:$A$27)-ROW($A$1)/($A$2:$A$27=$K$2)/(INDEX($E$2:$I$27,0,MATCH($K$2,$E$1:$I$1,0))<>""),ROWS($1:1))),"")

Regards
Bosco

 

[Juniad]

Thanks a lot.

 

[Juniad]

I want to apply it on MS 2007.. Sorry for inconvenience...

 

[bosco_yip]

I want to apply it on MS 2007.. Sorry for inconvenience...

For excel 2007 or below

In E3, CSE formula (confirm entered with CTRL+SHIFT+ENTER) copied across and down :

=IFERROR(INDEX(B$2:B$27,SMALL(IF(($A$2:$A$27=$K$2)*(INDEX($E$2:$I$27,0,MATCH($K$2,$E$1:$I$1,0))<>""),ROW($A$2:$A$27)-ROW($A$1)),ROWS($1:1))),"")

Regards
Bosco