Vlookup for matching a list of identifiers within a cell

Alright, this is going to be a little confusing, so I hope that I can make this coherant and someone can end my confusion in trying to figure this out.

So I have a bunch of adresses in a column that contain various client locations that look something like these:

Corporate Park - NEW DELHI-110065
SEOUL, KR
1883 SYDNEY 2000

We need to know the country these addresses are reffering to and for some reason or another that information is lacking. If I have a list that defines each of these to what country they match such as:

New Deli India
Seoul South Korea
Sydney Australia


Is there a way to cross reference the original adress column to my definition sheet? I basically want to do an If statement that goes If(each cell in the adress column contains any of the markers from the definition list, give me that defined country, give me NA)

I know this seems simple, but I have thousands of cells with unknown countries and cannot go through individually.

Thanks so much if anyone is able to crack the code.

Thanks for your reply.

I have attached a small sample of adresses with a brief explanation of what I am looking for. It’s a little more complicated than this, but I basically just need to know if there is a formula that can search through an individual cell, look for a specific text string that I have defined in a seperate table, and return true or false (or a row number, etc that I define) as to whether any of the markers apear in each cell.

Let me know if this makes sense and thanks again.

Perhaps something like this, see attached.

Originally Posted by jarp22886

Thanks for your reply.

I have attached a small sample of adresses with a brief explanation of what I am looking for. It’s a little more complicated than this, but I basically just need to know if there is a formula that can search through an individual cell, look for a specific text string that I have defined in a seperate table, and return true or false (or a row number, etc that I define) as to whether any of the markers apear in each cell.

Let me know if this makes sense and thanks again.

Also Try This

Just copy and Paste this Formula

=LOOKUP(1E+300,SEARCH(F$3:F$15,A2),G$3:G$15)