Multiple excel formulas

Question

Ok, so I am first using the following formula to get my account string:

=IF(H20>=400,"8"&RIGHT(S20,4),S20)

This turns my account number from a 78899 into a 88899. Unfortunately that account doesn't exist. The 78899 is a default number that needs to be changed to a 88000 or a 88010 depending on the description in another column of excel. How do I maintain the original formula (because that formula works to change account numbers that already in a 78010 format that gets changed to a 88010 format. I need the first formula to also be connected with a secondary formula to look up key words in a field, i.e. pizza, or airline to then change the 88899 to a 88000 or 88010.

you may have to use a series of nested if statements to get what you are looking for — happymacarts
– happymacarts, Commented Dec 22, 2016 at 17:13

Fred · Accepted Answer · 2016-12-22 17:15:56Z

1

It sounds like you need a nest if with an and statement, if I understand correctly......something like:

=IF(AND(S20=78899,I20="pizza"),88000,IF(AND(S20=78899,I20="airline"),88010,IF(H20>=400,"8"&RIGHT(S20,4),S20)))

You did not specify where "pizza" or "airline" might be so I used I20, but you could change to fit your needs.

answered Dec 22, 2016 at 17:15

Fred

1851 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

J1983 Over a year ago

That's great! How would I update to look for key words in the a cell, i.e. the "pizza" in Pizza Hut or the word "airline" in American Airline.

Fred Over a year ago

=IF(AND(S20=78899,NOT(ISERROR(SEARCH("pizza",I20)))),88000,IF(AND(S20=78899,NOT(ISERROR(SEARCH("airline",I20)))),88010,IF(H20>=400,"8"&RIGHT(S20,4),S20)))

Fred Over a year ago

If this helped, could you please mark as answered? Thanks

J1983 Over a year ago

The second part of the logic is not pulling through...i.e NOT(ISERROR(SEARCH("")

J1983 Over a year ago

=IF(AND(S21=78899,NOT(ISERROR(SEARCH("Air",G21)))),88000,I‌F(AND(S21=78899,NOT(ISERROR(SEARCH("Robin",G21)))),88010,I‌F(H21>=400,"8"&RIGHT‌(S21,4),S21)))

|

Collectives™ on Stack Overflow

Multiple excel formulas

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related