filter function that skip blank cells

[ExcelNewbie2020]

Sirs/Ma'ams,

I have a wide range of data let say from A to Z with specific column headers. I would like to create a filter function that will show me the values under each column header. I already used this function =FILTER(A2:F4,A1:F1=B9) but it replaced blank cells with "0". Is there any function that will skip blank cells?

 

[Alex Blakenburg]

Someone else may have a shorter way of doing this but here is one option:

=FILTER(FILTER(A2:F4,A1:F1=B9),FILTER(A2:F4,A1:F1=B9)<>"")

And to save time recreating the data if they want to try:

Book1
	A	B	C	D	E	F
1	A	B	C	D	E	F
2	4	1		2	6	5
3	4		3	2	6
4		1	3	2		6
5
6
7
8
9	Filter	B
10		1
11		1
Sheet1

Cell Formulas
Range	Formula
B10:B11	B10	=FILTER(FILTER(A2:F4,A1:F1=B9),FILTER(A2:F4,A1:F1=B9)<>"")
Dynamic array formulas.

 

[Fluff]

Slightly shorter

=LET(f,FILTER(A2:F4,A1:F1=B9),FILTER(f,f<>""))

 

[ExcelNewbie2020]

thanks guys, it works as expected..

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.