Excel IF Statement not working

New Contributor

I have a workbook with 2 worksheets.  I want the IF statement to look at the second worksheet and if a certain number is in the list to return a yes, if not a no.  what I have is:

=IF('Direct-Bills-4-16-17'!A:A=B6, "Yes", "No")

Am I using the wrong function?  It seems to work for 192 rows then just puts no in every cell, even if it should be yes.

Really appreciate any ideas/help anyone can give me.

Thank you,


6 Replies
best response confirmed by Conchita Ballard (New Contributor)

Hi Conchita,


Please try this formula instead:

=IF(ISNUMBER(MATCH(B6,'Direct-Bills-4-16-17'!A:A,0)), "Yes", "No")


I hope this helps you


Hi Conchita,


It depends on how your data is structured. If that formula is in row 6, column A of first sheet, it takes from exactly the same row 6 if the second sheet and compares with B6. If you drag that formula down it takes values in rows 7 and compares with B7.


Is that your scenario?

Thank you so very much!  It worked perfect!

Thank you for working on this for me.  No, that was not the issue.  See the reply from Haytham Amairah  above.  His formula worked for my problem.  Again, thank you.


Again, in general it depends on how your data is structured. In particular, you have no repeated values in second sheet.


Anyway, so far so good if works.

I was curious if anyone has an if statement that can time stamp? Currently I am using =IF(A2<>"", NOW(), "") however, when I update cell B2 or C2 it will still change the time in the cell with the formula. Office 365 issue?