IF Statement Excel

Hello,

I would like to check to see whether the value in Cell A3 is present at all in the range of cells (A2:N24) on the Week1 worksheet as below.


=IF(A3=('Week 1'!A2:N24),"Yes","No")

However it always returns no because I think it is looking for the condition of that value being in every cell in that range?

Can someone please point me in the right direction on the right formula? I just want to confirm that the value present in cell A3 is in atleast one of the cells in range  'Week 1'!A2:N24'.

Regards,

GISVPN
gisvpnAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Try using COUNTIF like this

=IF(COUNTIF('Week 1'!A2:N24,A3)>0,"Yes","No")

regards, barry
0
 
JimFiveConnect With a Mentor Commented:
The only way I can think of to do this is to put a formula at the bottom of each column:
=vlookup(A3, A2:A24, 1,false)
This will put the value found in the cell or #NA if not found.

And then put a formula to look for success
=if(isna(Hlookup(A3,A25:N25,1,false)),"No","Yes")
0
 
JimFiveConnect With a Mentor Commented:
Barry's is better.  Nice Solution
0
 
andrew_manConnect With a Mentor Commented:
=IF(COUNTIF(Week 1'!A2:N24,A3),"Yes","No")

no need greater than 0
0
 
barry houdiniCommented:
Note that is is also possible to use OR like this

=IF(OR(A3='Week 1'!A2:N24),"Yes","No")

but that needs to be "array entered" with CTRL+SHIFT+ENTER so COUNTIF is probably preferable in most cases.

"no need greater than 0"

that's true andrew_man, but sometimes I think the added clarity outweights the small space saving!

regards, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.