Multiple nested if statements with the =IF(ISTEXT(SEARCH

[paintdrying]

So I just started cutting my teeth on some of the more complicated parts of Excel. I want to output one of a variety of text strings in a given cell based on a variety of text strings in a column to the the left. Some sort of fuzzy match would be excellent as well. I'm not using VBA either.

Practical example: output tiger ( in B5) every time Africa or India shows up in (A5), but also output horse of cow (in B5) whenever America or Europe show up in (A5).

Basically is would output a given text (of multiple possible) based on a a given text (of multiple) in the referencing cell .

<bdo dir="ltr">=IF(ISTEXT(SEARCH("Africa",A452)),A453,"lion") I believe this formula here will output lion whenever Africa Is put within the search. But how do I also say (within in the same cell) if the text happens to be America or Europe output cow </bdo>

 

[philwojo]

Your thinking is off, your test will always return "lion", the "Search" part of your formula is going to return a number, the starting number of Cell A452 if it contains the text you are looking for, in this case Africa. So it will return a 1, or an error if it doesn't match. Either way the "IStext" portion won't ever see text so your If statement will always give you a "False" result, and in your case that will always be "Lion".

You could try this and see if it does what you want though.

=IF(OR(A452="Africa",A452="India"),"Lion",IF(OR(A452="America",A452="Europe"),"Cow","Neither"))

I may not have fully understood what you want to test for and output, so it might need to be tweaked.

 

[paintdrying]

Your thinking is off, your test will always return "lion", the "Search" part of your formula is going to return a number, the starting number of Cell A452 if it contains the text you are looking for, in this case Africa. So it will return a 1, or an error if it doesn't match. Either way the "IStext" portion won't ever see text so your If statement will always give you a "False" result, and in your case that will always be "Lion".

You could try this and see if it does what you want though.

=IF(OR(A452="Africa",A452="India"),"Lion",IF(OR(A452="America",A452="Europe"),"Cow","Neither"))

I may not have fully understood what you want to test for and output, so it might need to be tweaked.

I think that helped alot. I will try and I really appreciate it!!

 

[paintdrying]

One more question, because your advice worked perfectly, this question is a little harder I believe. But what if we want to say we only want the text Africa to be part of the text in the cell on the left. for instance If it said South Africa, lion would still print. Or if It said United States of America, Cow would still print. I think I gets a bit complicated at that point but maybe you inderstand! thank you sir. your helping me do something at work!

 

[paintdrying]

One more question, because your advice worked perfectly, this question is a little harder I believe. But what if we want to say we only want the text Africa to be part of the text in the cell on the left. for instance If it said South Africa, lion would still print. Or if It said United States of America, Cow would still print. I think I gets a bit complicated at that point but maybe you inderstand! thank you sir. your helping me do something at work!

 

[philwojo]

Try this:

=IF(OR(ISNUMBER(SEARCH("Africa",A452)),A452="India"),"Lion",IF(OR(ISNUMBER(SEARCH("America",A452)),A452="Eurpoe"),"Cow","Neither"))

 

[paintdrying]

Do you have a way to pay you on bitcoin, as soon as I load my account ill send you 20$ . You saved me alot of time and I would like to help you in return

 

[paintdrying]

btw it worked. And Im very grateful

 

[philwojo]

Glad it worked, and I am glad to give back to this community that has helped me so much, so no payment is required, but the gesture is appreciated.

 

[paintdrying]

It seems to only work for the first value though i.e. Africa and America. The ones directly within the search. But Thank you because Im definately getting closer