IF ISERROR and FIND based on multiple criterias

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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,886
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

New Member
Joined
Apr 29, 2015
Messages
23
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,886
OK, Try this:

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

DIARYTODAY

New Member
Joined
Apr 29, 2015
Messages
23
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:ROFLMAO: I am not that advanced in Excel and your formula is crazy but you made my day.
 

DIARYTODAY

New Member
Joined
Apr 29, 2015
Messages
23
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,886
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.
 

Forum statistics

Threads
1,082,283
Messages
5,364,268
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top