Simple FILTER problem

[sharshra]

I have a table where I have to filter the ctg column having 'imp' or 'prd'. I tried doing this using formula, but not getting the correct results. I'm sure I must have done a silly mistake Can the experts help please?

Source table:

excel problems.xlsx
	B	C	D	E
54	#	desc	name	ctg
55	2	fsdfsf		imp
56	98	ewqe	bn
57	s-5	nmn	ui
58	43	vcvc	bcv	prd
59	v1	ewew	w	imp
filter

Desired output:

excel problems.xlsx
	G	H	I	J
54	#	desc	name	ctg
55	2	fsdfsf		imp
56	43	vcvc	bcv	prd
57	v1	ewew	w	imp
filter

Error using FILTER function:

excel problems.xlsx
	L
55	#CALC!
filter

Cell Formulas
Range	Formula
L55	L55	=LET( all,$B$55:$E$59, ctg, $E$55:$E$59, f, FILTER(all, (ctg="imp")*(ctg="prd")), f)

 

[RoryA]

You need + for OR. * is equivalent to AND - so:

=LET(all,$B$55:$E$59, ctg, $E$55:$E$59,f, FILTER(all, (ctg="imp")+(ctg="prd")),f)

 

[sharshra]

Thanks for the quick reply, @RoryA It works perfectly. Just one hitch In the output, blank cell is replaced by zero. How to avoid this? If it is blank, I need blank only in the output.

excel problems.xlsx
	L	M	N	O
55	2	fsdfsf	0	imp
56	43	vcvc	bcv	prd
57	v1	ewew	w	imp
filter

Cell Formulas
Range	Formula
L55:O57	L55	=LET( all,$B$55:$E$59, ctg, $E$55:$E$59, f, FILTER(all, (ctg="imp")+(ctg="prd")), f)
Dynamic array formulas.

 

[RoryA]

You can work around that with:

=LET(all,$B$55:$E$59, ctg, $E$55:$E$59,f, FILTER(all, (ctg="imp")+(ctg="prd")),IF(f="","",f))

 

[sharshra]

Fantastic!!! Thanks a lot, @RoryA