Need help with IF(MATCH formula

[zazybang]

I am trying to use an IF statement to place this or that based on the criteria. For the criteria i am using a MATCH statement to see if a cell matches a individual cell amongst a range. The issue i have is that when the criteria is false it does not use the false function of the IF statement instead it says N/A, however when the Match statement is true it works properly. Formula: =IF(MATCH(J6,B6:B41,0),"Yes","No")

 

[snjpverma]

Welcome to the forum Zazybang,

Try this

=IF(COUNTIF(B6:B41,J6),"Yes","No")

 

[zazybang]

I love you <3

 

[Aladin Akyurek]

I am trying to use an IF statement to place this or that based on the criteria. For the criteria i am using a MATCH statement to see if a cell matches a individual cell amongst a range. The issue i have is that when the criteria is false it does not use the false function of the IF statement instead it says N/A, however when the Match statement is true it works properly. Formula: =IF(MATCH(J6,B6:B41,0),"Yes","No")

Add the ISNUMBER test for MATCH returns a position indication number if successful, otherwise #N/A. ISNUMBER ignores error values...

=IF(ISNUMBER(MATCH(J6,B6:B41,0)),"Yes","No")

This particular formula can be expressed more succinctly:

=ISNUMBER(MATCH(J6,B6:B41,0))+0​

1 as result means a hit, 0 a miss.

By the way, ISNUMBER(MATCH(... is faster than COUNTIF(S).