• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please help me with creating an IF nested formula

Fernandez30

New Member
Hi,

Please help me creating a formula, what i need is if a cell A2 contains a word "BRA" then it should give me the result as "Celma" so on and so forth.

BRA - CELMA, GBR- Caledonia, HUN - Hungary, USA - Lafayette, LHR - London, MYS- Malaysia, TWN - Taiwan, Anything else - Others

I created a formula however it is not working :(

=IFERROR(IF(FIND("BRA",A2,1),"Celma",IF(FIND("GBR",A2,1),"Caledonia",IF(FIND("HUN",A2,1),"Hungary",IF(FIND("USA",A2,1),"Lafayette",IF(FIND("LHR",A2,1),"London",IF(FIND("MYS",A2,1),"Malaysia",IF(FIND("TWN",A2,1),"Taiwan"," "))))))),"Others")

Can somebody please help me.
 

Attachments

vletm

Excel Ninja
Fernandez30
Where is 4?
If You would mean that those 'three letters' would in any position then there could be some challenges ... there could be eg text BRA anywhere!
In Your sample file, those 'three letters' are ALWAYS from 4th!
 

shrivallabha

Excel Ninja
With the sample data provided, you can use following formula in cell C2.
=IFERROR(MID(SUBSTITUTE(LOOKUP(2^15,FIND("-"&LEFT($D$1:$J$1,3)&"-",A2&"-",1),$D$1:$J$1)," ",""),5,99),"Others")
Copy down the formula.
 

Fernandez30

New Member
With the sample data provided, you can use following formula in cell C2.
=IFERROR(MID(SUBSTITUTE(LOOKUP(2^15,FIND("-"&LEFT($D$1:$J$1,3)&"-",A2&"-",1),$D$1:$J$1)," ",""),5,99),"Others")
Copy down the formula.
Would you mind explaining what is 2^15 :what does this mean
 

shrivallabha

Excel Ninja
Would you mind explaining what is 2^15 :what does this mean
"-"&LEFT($D$1:$J$1,3)&"-" builds a string like -BRA-

Which is then used in FIND function to locate in source string through FIND("-"&LEFT($D$1:$J$1,3)&"-",A2&"-",1)
Here the dash after A2 is added to handle if the string exists at end of source string.
This returns an array like {15,#VALUE,#VALUE,#VALUE...} i.e. where string is located it returns position of the string and #VALUE if it doesn't.

This ARRAY is then checked against maximum value of 2^15 (32768 which is one more than Excel cell specification limit 32767) to return the numerical result in above array (see the bold underlined value). You can use any large number instead of 2^15 e.g. 999 which should be higher than the number of maximum characters you may encounter.

This returns a result like BRA - Celma where we need to get rid of bold-underlined portion which we do by using one more SUBSTITUTE (as your data is not fully consistent) and MID functions.

Finally, we include cover specific cases i.e. $D$1:$J$1 so that the formula shall return error in other cases where we substitute with word others in all such cases using IFERROR.

You can see and test this all using Formula Evaluate option.
 

Fernandez30

New Member
Thank you so much for explaining it so beautifully. I did try the evaluate formula option was not able to understand 2^15, so thought of asking you.

Thanks a ton for all your time and effort on solving my problem.

Cheers!
 
Top