Filtering arrays in excel

Question

Let's say I have the following data in excel:

      A      B
1  Tomato   Car
2  Apple    Hat
3  Tomato   Melon
4  Tomato   Paper

Using the following array formula in excel:

{=IF(A1:A4="B";B1:B4)}

I am able to get the following array

{Car,FALSE,Melon,Paper}

How do I get rid of the 'FALSE' value, so that I end up with

{Car,Melon,Paper}

Answer 1

Modify the formula to get a null, using the False argument in the IF sentence.

Something like this should work for you:

=IF($A$1:$A$4="Tomato";$B$1:$B$4;"")

This is what I get after applying formula: