IF ISERROR and FIND based on multiple criterias

[DIARYTODAY]

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

 

[Scott Huish]

In your first formula, you are concatenating the strings AEM and ADVANCED, so your search string is AEMADVANCED, which is not what I think you want.
Why are you using FIND instead of just = ?

Does this do what you want?

=IF(B1="AEM-ADVANCED","AEM",LEFT(B1,3) & " - ADV")

 

[DIARYTODAY]

Thank you Scott, that formula wont work because AEM and AEP is just 2 clients out of 100 client names I have (ie. AEM-xxxx, RRC-xyxyxy, VPC-yyyy, AEP-zizizi, CLP-ghghghh, PLM-shshshhs,..and many more). All prospects can be distinguished by the 3 letter abbreviation for that client..

So while this formula works for me:
=IF(ISERROR(FIND("ADVANCED",B1)),LEFT(B1,3),LEFT(B1,3)&" - ADV") which will find those with "ADVANCED" and give me RRC - ADV, or PLM - ADV, etc which is what I want.

Then I realize it could not find AEM, so I need Excel to find AEM also and interpret as AEP since they are same client, just diff entity. Does that make sense?

 

[Scott Huish]

OK, Try this:

=IF(LEFT(B1,3)="AEM","AEP",LEFT(B1,3))&IF(ISNUMBER(FIND("ADVANCED",B1))," - ADV","")

 

[DIARYTODAY]

OK, Try this:

=IF(LEFT(B1,3)="AEM","AEP",LEFT(B1,3))&IF(ISNUMBER(FIND("ADVANCED",B1))," - ADV","")

It works. You are Genius !!!!!! Thanks a bunchh I am not that advanced in Excel and your formula is crazy but you made my day.

 

[DIARYTODAY]

Hi Scott,

So I can better understand how your formula works, can you explain the 2nd phrase IF(ISNUMBER(FIND("ADVANCED",B1))," - ADV","")? why"" at the end?

 

[Scott Huish]

If it finds the text ADVANCED, it will return a number of the position of where it found it. So if a number is returned, it found the text and we will add " - ADV", otherwise we will add an empty string, essentially nothing, to the end if it doesn't find it.

 

[DIARYTODAY]

Thank you