Forum Discussion

Jimbobmcwalton's avatar
Jimbobmcwalton
Brass Contributor
Feb 26, 2019

IF Function

Hi There, 

 

I am trying to get Excel to show a result of 3 in a cell if 3 different cells contain WORDONE WORDTWO & WORDTHREE

 

Im using  

 

=IF(OR(V4="WORDONE",W4="WORDTWO",X4="WORDTHREE"),3,1)

 

The formula keeps returning a result of 1 no matter if I use AND (or) OR 

 

Any ideas?

excel
Formulas and Functions
  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Feb 26, 2019
    Check the values of V4:X4. None of them might be equal to any of the values specified in the arguments of the OR function.
    • Jimbobmcwalton's avatar
      Jimbobmcwalton
      Brass Contributor
      Feb 26, 2019

      Thank you - just checked and the WORDTHREE was actually in capitals and the formula was Wordthree which was causing this !

       

       

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Feb 26, 2019

        That doesn't matter, comparison here is not case sensitive. Try

        =IF("WORDTHREE"="Wordthree", "Same", "Different")
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Feb 26, 2019

    Your exact formula returns 3

    but perhaps you need something like

    =(V4="WORDONE")+(W4="WORDTWO")+(X4="WORDTHREE")

    which returns the number from zero to 3, depends on how many matches. That's if order of words matters, If not, it'll be another formula.

Resources