Home

Excel Formula - formula based on if cell contains

%3CLINGO-SUB%20id%3D%22lingo-sub-214113%22%20slang%3D%22en-US%22%3EExcel%20Formula%20-%20formula%20based%20on%20if%20cell%20contains%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-214113%22%20slang%3D%22en-US%22%3EHi%20all%2C%3CBR%20%2F%3E%3CBR%20%2F%3ESeeing%20if%20any%20of%20you%20would%20be%20assist%2C%20I%20am%20slowly%20learning%20some%20advance%20formulas%20in%20Excel%20and%20wanted%20to%20see%20if%20the%20following%20was%20possible.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20A1%20%3D%20Yes%20and%20B1%20%3D%20Yes%2C%20then%20C1%20would%20be%20%E2%80%9CN%2FA%E2%80%9D%3CBR%20%2F%3EIf%20A1%20%3D%20Yes%20and%20B1%20%3D%20No%2C%20then%20C1%20would%20be%20%E2%80%9CP3%E2%80%9D%3CBR%20%2F%3EIf%20A1%20%3D%20No%20and%20B1%20%3D%20No%2C%20then%20C1%20would%20be%20%E2%80%9CP2%E2%80%9D%3CBR%20%2F%3E%3CBR%20%2F%3EYour%20help%20would%20be%20muchly%20appreciated%20as%20I%20get%20a%20grips%20to%20more%20advanced%20formulas%20as%20I%20have%20just%20learnt%20how%20to%20use%20IF%20formulas%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks%3CBR%20%2F%3EGV%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-214113%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-214627%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20formula%20based%20on%20if%20cell%20contains%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-214627%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Haytham%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20make%20a%20good%20call%20out%20and%20Gareth%20has%20two%20different%20ways%20to%20do%20the%20same%20thing%20for%20his%20learning%20benefit.%3CBR%20%2F%3E%3CBR%20%2F%3EGreat%20team%20effort!%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3CBR%20%2F%3EDamien%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-214401%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20formula%20based%20on%20if%20cell%20contains%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-214401%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Gareth%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20use%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DIF(AND(A1%3D%22Yes%22%2CB1%3D%22Yes%22)%2C%22N%2FA%22%2C%3CBR%20%2F%3E%20IF(AND(A1%3D%22Yes%22%2CB1%3D%22No%22)%2C%22P3%22%2C%3CBR%20%2F%3E%20IF(AND(A1%3D%22No%22%2CB1%3D%22No%22)%2C%22P2%22%2C%22%22)))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F89704%22%20target%3D%22_blank%22%3E%40Damien%20Rosario%3C%2FA%3E%3C%2FP%3E%3CP%3EYour%20formula%20works%20just%20fine%2C%20but%20%3CSTRONG%3EIFS%3C%2FSTRONG%3E%20function%20isn't%20available%20in%20all%20versions%20of%20Excel!%3C%2FP%3E%3CP%3EIt%20currently%20available%20only%20for%20Office%20365%20subscribers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-214171%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20formula%20based%20on%20if%20cell%20contains%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-214171%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Gareth%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sure%20someone%20will%20probably%20have%20a%20better%20way%20to%20do%20this%20but%20this%20should%20work%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(IFS(AND(A1%3D%22Yes%22%2CB1%3D%22No%22)%2C%20%22P3%22%2C%20AND(A1%3D%22Yes%22%2CB1%3D%22Yes%22)%2C%20%22N%2FA%22%2C%20AND(A1%3D%22No%22%2CB1%3D%22No%22)%2C%20%22P2%22)%2C%20%22Not%20a%20valid%20combo%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20used%20IFS%20for%20multiple%20conditions%20and%20have%20also%20added%26nbsp%3B%20IFERROR%20to%20catch%20any%20invalid%26nbsp%3Bcombos%20or%20entries.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps.%20Sample%20file%20is%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3EDamien%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor
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

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

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

Highlighted

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