VLOOKUP a full text in a partial data field

[lukeareed]

Hi,

I am trying to look up a whole value in a partial data field, pulling through the partial data. For example cell A1 includes product description followed by location, I am trying to pull through just the location into B1 using the data from sheet 2.

Eg. Sheet 1 Sheet 2
Column A Column B Data
Stapler, UK UK UK
Pencil, UK UK Germany
Stapler, Germany Germany etc...
Pencil, Germany Germany


I have tried using wildcards e.g. =VLOOKUP("*"&A1&"*",Sheet2!A:A,1,0) but have had no joy.

Any advice would be great.

Many Thanks,
Luke

[Bernie Deitrick]

You need to post what you have on Sheet 1 and what you have on Sheet 2 in separate tables. Show what you have, and then also show what you want in a third table.

[lukeareed]

Sorry I didn't realise how messed up the formatting gets.

Screen Shot 2014-03-28 at 14.28.09.png

Not sure if that has uploaded correctly, but basically in column 1 it will have the product description e.g. "Stapler, London, UK". In the next column I want to extract only the Country, which will vary, (in this case UK).

In a separate sheet I will have a table with all the possible Countries, so I was wondering whether there is a way to look up the product description form column 1 and find a partial match in the table of countries, and if so pull through that country into column 2?

[Bernie Deitrick]

In Cell B2, array enter (enter using Ctrl-Shift-Enter instead of just Enter) this formula

=MID(A2,MAX(IF(ISERROR(SEARCH(",",A2,ROW(INDIRECT("A1:A"&LEN(A2))))),0,SEARCH(",",A2,ROW(INDIRECT("A1:A"&LEN(A2))))))+2,LEN(A2))

It will return whatever is after the last , in the cell, just in case you actually have

Stapler, London, UK

instead of just

Stapler, UK