TOCOL instead of...FILTER????

 

TOCOL instead of…FILTER???

 

This is a new trick I invented.

Suppose you have 2 lists and you want to extract only the matching values in these lists:

                                                Pic 1: 2 lists to compare

So, the most obvious solution would be using the FILTER function:

Pic 2: Using the FILTER function to display only matching values

 

But I invented a "cool" trick with the TOCOL function.
First we write a simple IF formula, where the FALSE is an invalid value (NA), which Excel interprets as an error:

          Pic 3: the IF function returns an error when there's no match

Now, what's left for us to do is to "wrap" the IF with the TOCOL function, where the second argument to the TOCOL function
is 2: ignore errors.

And the final result:

Pic 4: wrapping the IF function with TOCOL yields the desired solution

Simple, isn't it?