Map and append data in rows based on another sheets rows

[sunnykeerthi]

Hi,

I've 2 Excel sheets as below. In my first sheet, I've First name, Last name and City.
In the 2nd, I got Address 1, City, State, Zip code and Country.

Here I want to match the City in sheet 2 with the city in sheet 1(it should be a contains, not equals to) and paste the entire row from sheet 2 in sheet 1 next to the city.

Names and Cities.xlsx
	A	B	C
1	First Name	Last Name	City
2	John	Wick	New york
3	George	Phillip	Cincinati
4	Karen	D'souza	Cleveland
5	Ravi	Malhotra	Saint Paul
6	Ryan	Miller	Houston
Sheet1

Names and Cities.xlsx
	A	B	C	D	E
1	Addr1	City	State	Zip	Country
2	2885 Morningview Lane	New York	NY	10013	US
3	757 Grey Fox Farm Road	Houston	TX	77002	US
4	999 Corbin Branch Road	Cleveland	TN	37311	US
5	321 Rogers Street	Cincinati	OH	45236	US
6	3097 Hayhurst Lane	Saint Paul	MN	55121	US
Sheet2

The output should be like this.

Names and Cities.xlsx
	A	B	C	D	E	F	G	H
1	First Name	Last Name	City
2	John	Wick	New york	2885 Morningview Lane	New York	NY	10013	US
3	George	Phillip	Cincinati	321 Rogers Street	Cincinati	OH	45236	US
4	Karen	D'souza	Cleveland	999 Corbin Branch Road	Cleveland	TN	37311	US
5	Ravi	Malhotra	Saint Paul	3097 Hayhurst Lane	Saint Paul	MN	55121	US
6	Ryan	Miller	Houston	757 Grey Fox Farm Road	Houston	TX	77002	US
Sheet1

Sorry for asking the solution directly. I'm very new to Excel (I know this sounds very odd ?).
I tried VLOOKUP, but it gives only True/False, so as the MATCH does.

Thanks

 

[Psygrrl88]

Ok, FIRST - if you don't have office 365 or the newest one you need to move the cities column in the second sheet to in front of the address column. If you do have office 365 or the newest version you can use xlookup instead of vlookup and don't have to move anything.

Second - in the columns in sheet one where you want the information to go (EFGH in your example) you are going to put the following formulas;
basically: =Vlookup(sheet1citynamecell,'sheet2citynamecell'!XX,Columnindex (in your example the cell indexes would be 2,3,4, and 5 for city, state zip and country),FALSE (for exact match)
Ex:
E:=Vlookup(C2,'SHEET2NAME'!A2,2,FALSE) / (not entirely sure how to use Xlookup as I haven't used it before)
F:=Vlookup(C2,'SHEET2NAME'!A2,3,FALSE) /
G:=Vlookup(C2,'SHEET2NAME'!A2,4,FALSE) /
H:=Vlookup(C2,'SHEET2NAME'!A2,5,FALSE) /

Then - Copy thosecells and paste down to have the formula autofill for each row

 

[Psygrrl88]

you are getting an error because the vlookup value has to be in the front of the table for it to work.

 

[Fluff]

Hi & welcome to MrExcel.
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[sunnykeerthi]

Hi @Fluff ,

Thanks and I've updated the details.

 

[Fluff]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	First Name	Last Name	City
2	John	Wick	New york	2885 Morningview Lane	New York	NY	10013	US
3	George	Phillip	Cincinati	321 Rogers Street	Cincinati	OH	45236	US
4	Karen	D'souza	Cleveland	999 Corbin Branch Road	Cleveland	TN	37311	US
5	Ravi	Malhotra	Saint Paul	3097 Hayhurst Lane	Saint Paul	MN	55121	US
6	Ryan	Miller	Houston	757 Grey Fox Farm Road	Houston	TX	77002	US
Sheet1

Cell Formulas
Range	Formula
D2:H6	D2	=XLOOKUP(C2,Sheet2!$B$2:$B$6,Sheet2!$A$2:$E$6,"",0)
Dynamic array formulas.