Forum Discussion

Gareth Vincent's avatar
Gareth Vincent
Copper Contributor
Jul 11, 2018

Excel Formula - formula based on if cell contains

Hi all,

Seeing if any of you would be assist, I am slowly learning some advance formulas in Excel and wanted to see if the following was possible.

If A1 = Yes and B1 = Yes, then C1 would be “N/A”
If A1 = Yes and B1 = No, then C1 would be “P3”
If A1 = No and B1 = No, then C1 would be “P2”

Your help would be muchly appreciated as I get a grips to more advanced formulas as I have just learnt how to use IF formulas :)

Many thanks
GV
Formulas & Functions
Need Help

3 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Jul 11, 2018

    Hi Gareth,

     

    Please use this formula:

    =IF(AND(A1="Yes",B1="Yes"),"N/A",
     IF(AND(A1="Yes",B1="No"),"P3",
     IF(AND(A1="No",B1="No"),"P2","")))

     

    Hi Damien_Rosario

    Your formula works just fine, but IFS function isn't available in all versions of Excel!

    It currently available only for Office 365 subscribers.

     

    Regards

    • Damien_Rosario's avatar
      Damien_Rosario
      Silver Contributor
      Jul 11, 2018

      Hi Haytham

      You make a good call out and Gareth has two different ways to do the same thing for his learning benefit.

      Great team effort!

      Cheers
      Damien

  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor
    Jul 11, 2018

    Hi Gareth

     

    I'm sure someone will probably have a better way to do this but this should work:

     

    =IFERROR(IFS(AND(A1="Yes",B1="No"), "P3", AND(A1="Yes",B1="Yes"), "N/A", AND(A1="No",B1="No"), "P2"), "Not a valid combo")

     

    I've used IFS for multiple conditions and have also added  IFERROR to catch any invalid combos or entries.

     

    Hope that helps. Sample file is attached.

     

    Cheers

    Damien

Resources