Using 'IF' and 'THEN' formula.

[TheRadioBoy]

Hi, could someone please help me?

If I wanted to display set text depending on the entry in a certain cell, how would I go about that please?

Say in my entry cell of E7 the word 'INTERNAL' was input, I'd like the phrase 'ACCEPTED' to be displayed in cell E17. If 'EXTERNAL' was to be input then I'd like the phrase 'NOT ACCEPTED' to be displayed at E17.

Is this a fairly easy process that I can adapt to other text.

I am quite new to formulas and am just getting to grips with the basics.

Many thanks for any advice you can offer!

Rob

 

[AlanY]

in E17

=if(E7="INTERNAL","ACCEPTED","NOT ACCEPTED")

or

=IF(E7="INTERNAL","","NOT ")&"ACCEPTED"

 

[Joe4]

Try this in E17:

=IF(ISNUMBER(SEARCH("INTERNAL",E7)),"ACCEPTED",IF(ISNUMBER(SEARCH("EXTERNAL",E7)),"NOT ACCEPTED",""))

 

[TheRadioBoy]

Thank you very much! I really appreciate your help!

I suspected it would be something straightforward, I'm just a bit dim!

 

[Joe4]

Note the main two differences in the proposed solutions:

If the value in E7 is NOT "INTERNAL" or "EXTERNAL", Alan's solution will return "NOT ACCEPTED" while mine will return nothing (blank).

You didn't say what you wanted to happen if E7 was neither "INTERNAL" or "EXTERNAL", so we just made different assumptions on what you want to happen there. Choose the one that suits you best.

 

[TheRadioBoy]

Note the main two differences in the proposed solutions:

If the value in E7 is NOT "INTERNAL" or "EXTERNAL", Alan's solution will return "NOT ACCEPTED" while mine will return nothing (blank).

You didn't say what you wanted to happen if E7 was neither "INTERNAL" or "EXTERNAL", so we just made different assumptions on what you want to happen there. Choose the one that suits you best.

Again, many thanks - that's really helpful if any text other than 'INTERNAL' is now input I can display 'NOT ACCEPTED' ... I never considered that.

I'm somewhat astounded as to how adaptable Excel is - I never realised it was quite so powerful, for all different scenarios. I think I should get myself on a proper course or night-class.

Rob.

 

[Joe4]

You are welcome!

I'm somewhat astounded as to how adaptable Excel is - I never realised it was quite so powerful, for all different scenarios.

Yes, and there are often many different possible solutions to a problem, especially when you get into VBA.
It really is quite powerful and flexible!

 

[TheRadioBoy]

Yes, and there are often many different possible solutions to a problem, especially when you get into VBA.
It really is quite powerful and flexible!

Oh yes, I'd love to learn VBA too!

Taking your formula suggestion further, can that input string be expanded to have multiple conditions that would return 'ACCEPTED' and 'NOT ACCEPTED' for instance having 'INTERNAL', 'IN-HOUSE' or 'LOCAL' as the inputs for E17 to return 'ACCEPTED' and say 'EXTERNAL', 'OUTSOURCED' or 'NATIONAL' that would return 'NOT ACCEPTED'.

It gives me scope then to look at using that formula for other text inputs.

 

[Joe4]

Yes, you can keep nesting IF statements, like I did in my example.
Take a look at my reply and this link here: https://www.ablebits.com/office-addins-blog/2016/12/07/nested-if-excel-multiple-conditions/
and see if you can build off it to do what you want.

I will let you take a crack at it, it will be a good learning experience.
Post back here if you run into trouble.

 

[TheRadioBoy]

Yes, you can keep nesting IF statements, like I did in my example.
Take a look at my reply and this link here: https://www.ablebits.com/office-addins-blog/2016/12/07/nested-if-excel-multiple-conditions/
and see if you can build off it to do what you want.

I will let you take a crack at it, it will be a good learning experience.
Post back here if you run into trouble.

Many thanks, I will give it go!

Rob