DIARYTODAY
New Member
- Joined
- Apr 29, 2015
- Messages
- 23
Hello, please help me, im stuck.
So here is my table:
Table 1: EE Name(A1), prospect name (B1)(name is very long, i.e: AEM-xxxxxx, AEM-yyyyy, AEM-Advanced,etc..., AEP-xxxxx, AEP-zzzz,.... (AEM, AEP...are client names)
Table 2: EE Name (C1), prospect abbreviation (D1)
I need to abbreviate prospect names to calculate the rate for that client, AEM and AEP are same client (just diff entity), Advanced prospect has different rate so it has to be abbreviated as AEP- ADV, so my formula on D1 is:
=IF(ISERROR(FIND("AEM"&"ADVANCED",B1)),LEFT(B1,3),"AEP"&LEFT(B1,3)&" - ADV") It does not work
I also try
=IF(ISERROR(FIND("ADVANCED",B1)),LEFT(B1,3),LEFT(B1,3)&" - ADV")+IF(ISERROR(FIND("AEM",B1)),LEFT(B1,3),"AEP") but does not work either
thank you in advance
So here is my table:
Table 1: EE Name(A1), prospect name (B1)(name is very long, i.e: AEM-xxxxxx, AEM-yyyyy, AEM-Advanced,etc..., AEP-xxxxx, AEP-zzzz,.... (AEM, AEP...are client names)
Table 2: EE Name (C1), prospect abbreviation (D1)
I need to abbreviate prospect names to calculate the rate for that client, AEM and AEP are same client (just diff entity), Advanced prospect has different rate so it has to be abbreviated as AEP- ADV, so my formula on D1 is:
=IF(ISERROR(FIND("AEM"&"ADVANCED",B1)),LEFT(B1,3),"AEP"&LEFT(B1,3)&" - ADV") It does not work
I also try
=IF(ISERROR(FIND("ADVANCED",B1)),LEFT(B1,3),LEFT(B1,3)&" - ADV")+IF(ISERROR(FIND("AEM",B1)),LEFT(B1,3),"AEP") but does not work either
thank you in advance