How can I return blank cells as it is instead of printing as Zeros while using filter function

Question

["referring to the data shown in the image while using the filter function and getting the return values, How can I return blank cells as it is instead of printing as Zeros while using filter function"]

=FILTER(A2:C13,A2:A13=E1," ")

referring to the data shown in the image while using the filter function and getting the return values, How can I return blank cells as it is instead of printing as Zeros while using filter function

Answer 1

You could try:

---

• Formula used in cell E2

=LET(x,IF(A2:C13="","",A2:C13),FILTER(x,TAKE(x,,1)=E1))

---

Another possible way could be adding &"" in front of your array argument. Note that it will convert your numeric values to text so you can use a VALUE() function or multiple by 1 to convert it back to numeric when required for further calculation.

---

• Formula used in cell E8

=FILTER(A2:C13&"",A2:A13=E1," ")

---

Or, you can use as proposed by VBasic2008 Sir

---

• Formula used in cell E14

=FILTER(IF(A2:C13="","",A2:C13),A2:A13=E1," ")

---