Extract name from a column in different sheet

[Mr2017]

Hi

I have a list of addresses in column A of Sheet 1 in a file (please see the 9 rows below).

I will regularly need to populate the 'City' name in column B of Sheet 1, using the City names from column D of Sheet 2.

And I've been advised to do this manually, but I'm sure there's a formula that can do this!? Can anyone please advise?

Please find below sample data for Sheet 1 - there are only 9 rows (3 rows of 3 addresses).

I have also pasted data from Sheet 2 below (it's just column D, with the names of the Cities that I have to manually populate into column B of Sheet 1. I'd be grateful if anyone could help with this?

Sheet 1

Address	Location
24 Place des Reflets La Defense, Paris, France
24 Place des Reflets La Defense, Paris, France
24 Place des Reflets La Defense, Paris, France
50 E Randolph Street, Suite 7100, Chicago, Illinois, USA
50 E Randolph Street, Suite 7100, Chicago, Illinois, USA
50 E Randolph Street, Suite 7100, Chicago, Illinois, USA
10 St. James'S Square, London, United Kingdom
10 St. James'S Square, London, United Kingdom
10 St. James'S Square, London, United Kingdom

Sheet 2

			Paris
			Paris
			Paris
			Chicago
			Chicago
			Chicago
			London
			London
			London

 

[mehidy1437]

try below,

Book1
	A	B
1	Address	Location
2	24 Place des Reflets La Defense, Paris, France	Paris
3	24 Place des Reflets La Defense, Paris, France	Paris
4	24 Place des Reflets La Defense, Paris, France	Paris
5	50 E Randolph Street, Suite 7100, Chicago, Illinois, USA	Chicago
6	50 E Randolph Street, Suite 7100, Chicago, Illinois, USA	Chicago
7	50 E Randolph Street, Suite 7100, Chicago, Illinois, USA	Chicago
8	10 St. James'S Square, London, United Kingdom	London
9	10 St. James'S Square, London, United Kingdom	London
10	10 St. James'S Square, London, United Kingdom	London
Sheet2

Cell Formulas
Range	Formula
B2:B10	B2	=INDEX(Sheet7!$D$2:$D$110, MATCH(1, COUNTIF(A2, "*"&Sheet7!$D$2:$D$110&"*"), 0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Mr2017]

Oh, wow!!!

That's amazing, @mehidy1437 !!!

That worked!!

Thanks ever so much!!!

 

[mehidy1437]

You are welcome, thanks for the feedback.

 

[Mr2017]

No problem!

Thank you!!