 • 665K Members
• 5,719 Online
• 818K Conversations

## Excel Formula - formula based on if cell contains

Highlighted
Occasional Visitor

# 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
3 Replies
Highlighted

# Re: Excel Formula - formula based on if cell contains

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

Highlighted

# Re: Excel Formula - formula based on if cell contains

Hi Gareth,

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

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

Highlighted

# Re: Excel Formula - formula based on if cell contains

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