Excel 2007 : Nested IF Statements and string text

I am trying to write a formula to search through text of world regions in column A and write an abbreviation of the regions in column B. I have two problems. The first is there are 8 regions and nested IF statements will only work for 7 statements. The second is that even though I have written a formula for 7, it will only work for the first 3. The rest of returned with #NAME? I have been struggling for over an hour. Any suggestions?

Below is an example of what I am getting, followed by the formula I am using:

Column A -----> Column B

1EET883A - SINGAPORE PRODUCTION -----> SGP
3345DDAI - US GENERAL -------> US
IVSX225200B - THAILAND GENERAL -----> #NAME?

=IF(ISNUMBER(SEARCH("SINGAPORE",A2)),"SGP",IF(ISNUMBER(SEARCH("US",A2)),"US",IF(ISNUMBER(SEARCH("JAPAN",A2)),"JP",IF(ISNUMBR(SEARCH("CHINA",A2)),"CHINA",IF(ISNUMBER(SEARCH("THAILAND",A2)),"THAI",IF(ISNUMBER(SEARCH("MALAYSIA",A2)),"MAL",IF(ISNUMBER(SEARCH("PHILIPPINES",A2)),"PHIL", "")))))))

If you create a separate List of all the countries and there specific abbreviations in adjacent column, then you can use something like:

=LOOKUP(2,1/ISNUMBER(SEARCH(" "&$E$2:$E$10&" ",A2)),$F$2:$F$10)

where E2:F10 contains the countries and abbreviations, to look for in A2

then copy down after adjusting ranges to suit.

If you want blank instead of #N/A error when match not found, then use:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",LOOKUP(2,1/ISNUMBER(SEARCH(" "&$E$2:$E$5&" ",A1)),$F$2:$F$5)))

copied down.

Obviously NBVC's formula works, and if you want to use that additional lookup table great. If not, you can hard code the countries like so:

Please Login or Register  to view this content.

Based on your adjusted formula, I'm assuming your country full names are in B2:B10, and your abbreviations are in C2:C10. The text string you're searching is in A2, and you're putting this formula in some other cell that is not part of A2, B2:B10 or C2:C10?

If so, then it works for me.