Search for Text within Text

[John Bates]

Hi Fellow Excel'rs,

I am looking for a formula to search within a cell for text that matches the text I have in a list. If it matches, then I want it to return the value in my list offset by to columns. In the attached spreadsheet, If cell B4 contains text that matches any of the values in column K, then return the country in column M. I then want to drag this formula down column C.

Thanks so much!
John

[zbor]

How about this?

Book1(1).xls

This assume that your code is 2 characters before :

[zbor]

Another approach (with more correct answers) would be to find first zero and look 2 characters after it:

=INDEX($M$4:$M$270;MATCH(MID(B4;FIND("0";B4;1)+1;2);LEFT($L$4:$L$270;2);0); )

(confirmed with ctrl+shift+enter

(difference only in FIND("0";B4;1)+1;2 part)

[zbor]

Book1(1)(2).xls

But I see error in last row wich from word %22%20style% style consider as sao tome and principe...

[John Bates]

That works great!!! Thank you so much!!