Nested formulas

[koreilly]

I am trying to create a formula that includes the following

=IF(T2=0,IF(ISNUMBER(SEARCH("Redemption",E2)),"Redemption",IF(ISNUMBER(SEARCH("Subscription",E2)),"Subscription",)))

HOWEVER, if T2 does not equal 0, it will equal "Exchange" but i need it to also look at cell P2, and if p2 contains the word "Index" I need the formula to return "Exchange - Index"

Can someone help? do i need to do this in pieces, or is it possible to just have one formula?

 

[tyija1995]

Re: Excel Help, nested formulas

Hey,

Give this a try:
=IF(T2=0,IF(ISNUMBER(SEARCH("Redemption",E2)),"Redemption",IF(ISNUMBER(SEARCH("Subscription",E2)),"Subscription","")),IF(ISNUMBER(SEARCH("Index",P2)),"Exchange - Index", "Exchange"))

 

[Finalfight40]

Re: Excel Help, nested formulas

Like this?

=IF(T2=0,IF(ISNUMBER(SEARCH("Redemption",E2)),"Redemption",IF(ISNUMBER(SEARCH("Subscription",E2)),"Subscription",)),"Exchange"&IF(ISNUMBER(SEARCH("Index",P2))," - Index",""))

 

[Fluff]

Re: Excel Help, nested formulas

Hi & welcome to MrExcel.
How about
=IF(T2=0,IF(ISNUMBER(SEARCH("Redemption",E2)),"Redemption",IF(ISNUMBER(SEARCH("Subscription",E2)),"Subscription")),"Exchange"&IF(ISNUMBER(SEARCH("Index",P2))," - Index",""))

 

[koreilly]

Re: Excel Help, nested formulas

Hey,

Give this a try:
=IF(T2=0,IF(ISNUMBER(SEARCH("Redemption",E2)),"Redemption",IF(ISNUMBER(SEARCH("Subscription",E2)),"Subscription","")),IF(ISNUMBER(SEARCH("Index",P2)),"Exchange - Index", "Exchange"))

Thank you, unfortunately, didn't work because T2 did not equal zero.

 

[koreilly]

Re: Excel Help, nested formulas

This perfect - thank you!!!!!

 

[Eric W]

Re: Excel Help, nested formulas

You can shorten it a bit by using COUNTIF with wildcards instead of ISNUMBER(SEARCH like this:

=IF(T2=0,IF(COUNTIF(E2,"*Redemption*"),"Redemption",IF(COUNTIF(E2,"*Subscription*"),"Subscription","?")),"Exchange" & IF(COUNTIF(P2,"*Index*")," - Index",""))

 

[Fluff]

Re: Excel Help, nested formulas

This perfect - thank you!!!!!

Glad we could help & thanks for the feedback