operator can also be used with FILTER, like below: =FILTER(E9:E16,(F9:F16M16) * (F9:F16M17) * (F9:F16M18)) or specifying what we want inside " " =FILTER(E9:E16,(F9:F16"Blue") * (F9:F16"Silver") * (F9:F16"Green")) Returns range E9:E16 excluding those rows with criteria blue, silver and green in range F9:F16
Richard, both ISNA and NOT(ISNUMBER()) will return same results For example, if XMATCH return a list like below: 1 NOT(ISNUMBER()) will return FALSE / ISNA() will return FALSE 2 NOT(ISNUMBER()) will return FALSE / ISNA() will return FALSE #N/A NOT(ISNUMBER()) will return TRUE / ISNA() will return TRUE #N/A NOT(ISNUMBER()) will return TRUE / ISNA() will return TRUE Finally, what we want is to return unmatched values (rows 3 and 4)
@@richardhay645 Eachone is comfortable with their own way, I'm more comfortable with isNumber and NOT. Probably you were using ISNA more frequently. I will try in the future to TALK haha as I did those videos for test some months ago.
@@richardhay645 With respect, you come across as an entitled tosser, Richard. This guy has gone to the trouble of both giving you a free tut and has then gone further to answer your question in detail after your rude comment "Worthless..." etc. Damn - show a little grace and gratitude and your life will change mate. Cheers - Dave
THANK YOU! This formula is far more efficient than many of the examples I found while searching for a solution. Much appreciated
Really helped me loads!! Thank you
Nice one mate and thanks for going to the trouble. Saved me some time! Cheers - Dave
Excellent
Thank you!
What if we use "" to use not equal to
operator can also be used with FILTER, like below:
=FILTER(E9:E16,(F9:F16M16) * (F9:F16M17) * (F9:F16M18))
or specifying what we want inside " "
=FILTER(E9:E16,(F9:F16"Blue") * (F9:F16"Silver") * (F9:F16"Green"))
Returns range E9:E16 excluding those rows with criteria blue, silver and green in range F9:F16
Perfect!!!
Wothless. TALK. Why not ISNA
Richard, both ISNA and NOT(ISNUMBER()) will return same results
For example, if XMATCH return a list like below:
1 NOT(ISNUMBER()) will return FALSE / ISNA() will return FALSE
2 NOT(ISNUMBER()) will return FALSE / ISNA() will return FALSE
#N/A NOT(ISNUMBER()) will return TRUE / ISNA() will return TRUE
#N/A NOT(ISNUMBER()) will return TRUE / ISNA() will return TRUE
Finally, what we want is to return unmatched values (rows 3 and 4)
@Excel Professionally Of couse, that is my point. So why 2 functions rather than one? Plus why don't you talk???
@@richardhay645 Eachone is comfortable with their own way, I'm more comfortable with isNumber and NOT. Probably you were using ISNA more frequently. I will try in the future to TALK haha as I did those videos for test some months ago.
@@richardhay645 With respect, you come across as an entitled tosser, Richard. This guy has gone to the trouble of both giving you a free tut and has then gone further to answer your question in detail after your rude comment "Worthless..." etc.
Damn - show a little grace and gratitude and your life will change mate. Cheers - Dave