Forum Discussion

donny475's avatar
donny475
Copper Contributor
Sep 22, 2020

If formual

HI    I need to set up an if function to compare text results in my spreadsheet eg. if cell 80 has a P and cell 79 has a b than the result should be 1 , if Cell 80 has a P and cell 79 has  a P then...
excel
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Sep 22, 2020

donny475 Try something like this:

=IFERROR(IFS(AND(A79="b",A80="p"),1,AND(A79="p",A80="p"),-1),"")

Note: the search strings "b" and "p" are not case-sensitive. 

  • donny475's avatar
    donny475
    Copper Contributor
    Sep 22, 2020

    Thanks for that it worked but I also with in the same formulae need the reverse ie if a80 =b and a79 =P 1

    and if A80 =b and a79 =b -1 

     

     

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Sep 22, 2020

      donny475 

      Just add the extra conditions like this:

      =IFERROR(IFS(AND(A79="b",A80="p"),1,AND(A79="p",A80="p"),-1,AND(A79="p",A80="b"),1,AND(A79="b",A80="b"),-1),"")

       Or, if you intend to add a loot more combinations, consider using a lookup table. Then, the formula will become much shorter and easier to maintain. See the workbook attached for an example.

       

Resources