IF statement not returning correct answer

[KerriL]

Hello,

I am hoping someone can help me with this. Essentially, I am trying t
construct an If statement that will return "OK" or "NO" based on whethe
one cell is equal to any of another 5. All 6 cells in question ar
populated via a VLOOKUP function or a calculated formula. All the cell
themselves are populated correctly. However, the IF statement is no
able to correctly determine whether the one cell matches one of th
other five. To put it another way, if M1 is equal to H1, I1, J1, K1, o
L1, I want the formula to display "OK", and if M1 does not equal any o
those, I want it to display "NO". Sometimes the equation works, but 99
of the time it doesn't. I think it has something to do with how th
Vlookup/other formulas are formatting the numbers in the cells that th
IF statement is checking--but I can't figure out how. I've told all th
cells to format as number decimals with two digits--and the rows wher
the If statement works seem to be formatted the same as the rows wher
it doesn't.

I've tried two different IF statements, and both have the same issue. I
something wrong with my formulas, or is this a formating issue?

=IF(OR(M697=H697,M697=I697,M697=J697,M697=K697,M697=L697),"OK","NO")

=IF(M693=H693,"OK",IF(M693=I693,"OK",IF(M693=J693,"OK",IF(M693=K693,"OK",IF(M693=L693,"OK","NO")))))

Thank you very much for your help

 

[Spencer101]

Hello,

I am hoping someone can help me with this. Essentially, I am trying t
construct an If statement that will return "OK" or "NO" based on whethe
one cell is equal to any of another 5. All 6 cells in question ar
populated via a VLOOKUP function or a calculated formula. All the cell
themselves are populated correctly. However, the IF statement is no
able to correctly determine whether the one cell matches one of th
other five. To put it another way, if M1 is equal to H1, I1, J1, K1, o
L1, I want the formula to display "OK", and if M1 does not equal any o
those, I want it to display "NO". Sometimes the equation works, but 99
of the time it doesn't. I think it has something to do with how th
Vlookup/other formulas are formatting the numbers in the cells that th
IF statement is checking--but I can't figure out how. I've told all th
cells to format as number decimals with two digits--and the rows wher
the If statement works seem to be formatted the same as the rows wher
it doesn't.

I've tried two different IF statements, and both have the same issue. I
something wrong with my formulas, or is this a formating issue?

=IF(OR(M697=H697,M697=I697,M697=J697,M697=K697,M697=L697),"OK","NO")

=IF(M693=H693,"OK",IF(M693=I693,"OK",IF(M693=J693,"OK",IF(M693=K693,"OK",IF(M693=L693,"OK","NO")))))

Thank you very much for your help.

=IF(COUNTIF(H693:L693,M693)>0,"OK","NO"

 

[Spencer101]

Hello Spencer101

Thank you. I tried your formula in my sheet where I'm having the issue
but it did not work. I tried it in a test sheet where I typed in th
cell contents, and it did work. I think the problem is still that th
cells which are populated by formulas are somehow not formate
correctly. I think it might be that the cells contain the number out t
four decimals, but the number the IF statement is searching for only ha
two. I'm not sure how to fix this--I know I can format it to only sho
two decimals, and I can use the ROUND formula to round an individua
cell to two decimals, but how do I apply this to hundreds of cells
Thank you again for your help

An chance you could post a sample workbook
Either here or email me - pubnut @ gmail.co

You can add =ROUND() to the formulas that populate columns H to L, tha
way you could limit those to the same number of decimal places

 

[Ron Rosenfeld]

Hello,

I am hoping someone can help me with this. Essentially, I am trying to
construct an If statement that will return "OK" or "NO" based on whether
one cell is equal to any of another 5. All 6 cells in question are
populated via a VLOOKUP function or a calculated formula. All the cells
themselves are populated correctly. However, the IF statement is not
able to correctly determine whether the one cell matches one of the
other five. To put it another way, if M1 is equal to H1, I1, J1, K1, or
L1, I want the formula to display "OK", and if M1 does not equal any of
those, I want it to display "NO". Sometimes the equation works, but 99%
of the time it doesn't. I think it has something to do with how the
Vlookup/other formulas are formatting the numbers in the cells that the
IF statement is checking--but I can't figure out how. I've told all the
cells to format as number decimals with two digits--and the rows where
the If statement works seem to be formatted the same as the rows where
it doesn't.

I've tried two different IF statements, and both have the same issue. Is
something wrong with my formulas, or is this a formating issue?

=IF(OR(M697=H697,M697=I697,M697=J697,M697=K697,M697=L697),"OK","NO")

=IF(M693=H693,"OK",IF(M693=I693,"OK",IF(M693=J693,"OK",IF(M693=K693,"OK",IF(M693=L693,"OK","NO")))))

Thank you very much for your help.

If you are not getting a result of OK, then none of your equalities are evaluating to TRUE. In other words, although you may believe that M697=I697 (or one of the others), it does not.
The value stored in the cell does not change as a result of formatting, unless you have checked the option Precision as Displayed (which is almost never recommended).
To test for numeric equality, especially with calculated values, you should either test for a very small difference (depending on the level of precision you feel is suitable), or explicitly ROUND the values to your desired level of precision.