IF ISERROR and FIND based on multiple criterias

DIARYTODAY

New Member

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:

I also try

Scott Huish

MrExcel MVP
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?

DIARYTODAY

New Member
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:

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?

OK, Try this:

DIARYTODAY

New Member
OK, Try this:

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

New Member
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
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.

Thank you

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

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...