Cell contains text in data set

[GMan777]

I have a data set with AD accounts and their AD path that inculudes city & country (ex: OU=New York,OU=USA,OU=Corporate... another one OU=Boston,OU=USA,OU=Corporate, etc..)
I have a separate data set with only the higher level OU's, ie per country, (ex: OU=USA,OU=Corporate.. another one OU=Mexico,OU=Corporate, etc.) with other data.

I needed to do a Vlookup in the 1st data set with the city/country combo... if it contains the "OU=Country,OU=Corporate" in the 2nd data set... bring back a column in the 2nd data set.

I've seen ways to look for partial vlookup matches with "*", but in the other direction, which would work if my 1st list had only Country, and the 2nd data set had City/Country.

Maybe Vlookup is not the way.. but am stumped so far.

Any ideas?
Thanks in advance.

 

[maabadi]

Welcome to MrExcel Message Board.
Please upload example of Data at what you want. if you can use XL2BB it would be better. or upload images if you cann't.

 

[GMan777]

I have data set 1... and data set 2.
I'm looking for the right formula in column B highlighted...
- that if A:A ends with apple, looks up data set 2 bring back Red
- that if A:A ends with Banana, looks up data set 2 bring back Yellow
- that if A:A ends with Grape, looks up data set 2 bring back Green

So like a basic vlookup, but only looking to match the end of the string in data set 1 finds it's match in data set 2.
The number of characters vary, there is no standard special character like "right of" space or comma.. as the simplistic example I wrote below, but the "end" (ie. cell starting from the right) in A:A has a match in data set 2.

Appreciate any help/input you may have.

Thanks,

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff v2.xlsm
	A	B	C	D	E
1	County
2	West Yorkshire	Red		Yorkshire	Red
3	Greater Manchester	Blue		Manchester	Blue
4	County Durham	Green		Durham	Green
5	North Yorkshire	Red		Midlands	Purple
6	West Midlands	Purple
7	West Yorkshire	Red
8	North Yorkshire	Red
9	Greater Manchester	Blue
10	West Midlands	Purple
11
Main

Cell Formulas
Range	Formula
B2:B10	B2	=FILTER($E$2:$E$5,COUNTIF(A2,"*"&$D$2:$D$5))

 

[GMan777]

Thank you so much, it works like a charm. I had never used the filter function and was not familiar with it, thanks for the education !

 

[Fluff]

You're welcome & thanks for the feedback.