VLOOKUP and match return in whole column

[YellowTangerine]

Hello, I'm new here. Apologies ahead of I don't use the correct terminology.

I have data coming from two sources that I need to combine and match on a spreadsheet. The answers can't be returned in a single cell but match through and answered for each row in a column.

One source supplies just a name in one column and a unique code for each name in a second column.

The second source supplies the name (which matches the first source) but also an email address and another name associated with the name in the first source.

I need to bring all three pieces of information together into one worksheet and wondered if there is a way to use the VLOOKUP to populate cell in a column with a match, rather than just returning an answer in one cell.

Thank you in advance for your support.

 

[YellowTangerine]

Thank you. I'll go and see if I can replicate this. Sorry about my poor terminology. I need to match all the data so that each Name 1 column is correctly matched to the correct email address in the second source data. Hope that makes better sense?

I now understand. Thank you. How can I ensure the Unique ID is included in the result of the VLOOKUP please?

 

[YellowTangerine]

This is a simple VLOOKUP issue. I don't get what you mean by "match the whole column". You can automate it with getting index from column:

=VLOOKUP($A2,$H$2:$J$5,COLUMN(A1),0)

View attachment 86786

Thank you so much. I am uploading a sample data spreadsheet which should make it much clearer. This is a new area for me. I'm so grateful for your support. I hope you will be able to see the outcome I now desire and my apologies for the poor communication from my side. Please see below:

POC Data 050323.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Child Name	Unique Code			Child Name	Unique Code	Parent Name	Parent Email			Parent Name	Parent Emai	Child Name
2	Child 3	359fh4			#N/A	#N/A	#N/A				Name 1	test1@gmail.com	Child 1
3	Child 1	359fh5			#N/A	#N/A	#N/A				Name 2	test2@gmail.com	Child 2
4	Child 5	359fh6			#N/A	#N/A	#N/A				Name 3	test3@gmail.com	Child 3
5	Child 4	359fh7			#N/A	#N/A	#N/A				Name 4	test4@gmail.com	Child 4
6	Child 2	359fh8			#N/A	#N/A	#N/A				Name 5	test5@gmail.com	Child 5
7	Child 6	359fh9			#N/A	#N/A	#N/A				Name 6	test6@gmail.com	Child 6
8
9	Data Source 1				Result						Data Source 2
Sheet1

Cell Formulas
Range	Formula
E2:E7,G2:G7	E2	=VLOOKUP($A2:$A7,$K$2:$M$7,COLUMN(A1),0)
F2:F7	F2	=VLOOKUP($B2:$B7,COLUMN(B1),0)
Dynamic array formulas.

 

[YellowTangerine]

This post explains things better but it got locked as it's a duplicate question. If anyone can please help me O would be very grateful. Thank you.

Help with creating VLOOKUP formula

Hello, Would anyone please be able to help me construct the correct formula to create the desired results in the table below? I've not used VLOOKUP before so am a novice at this. Any support would be most gratefully received. Thank you. ABCDEFGHIJKLMNOPQ1Child NameUnique CodeChild Name...

www.mrexcel.com

 

[YellowTangerine]

Thank you. I will check this out later. Very much appreciate your support.

 

[Legacy 143009]

No problem

 

[YellowTangerine]

Yes it does. Thank you. I will look at both options tomorrow now. Much appreciated

 

[YellowTangerine]

No problem

This is perfect. Thank you so very much.

 

[YellowTangerine]

Is this what you are looking for?

Book1
	A	B	C	D	E	F	G	H
1	DATA SOURCE 1				RESULT AFTER COMBINING BOTH THE SOURCES
2	Child Name (Common)	Unique Code			Child Name (Common)	Unique Code	Parent Name	Parent EmaiL
3	Child 3	359fh4			Child 1	359fh5	Name 1	test1@gmail.com
4	Child 1	359fh5			Child 2	359fh8	Name 2	test2@gmail.com
5	Child 5	359fh6			Child 3	359fh4	Name 3	test3@gmail.com
6	Child 4	359fh7			Child 4	359fh7	Name 4	test4@gmail.com
7	Child 2	359fh8			Child 5	359fh6	Name 5	test5@gmail.com
8	Child 6	359fh9			Child 6	359fh9	Name 6	test6@gmail.com
9					(Directly copy pasted from data source 2)	(vlookup applied - looking up for unique codes from data source 1 against Child name in data course 2	(Directly copy pasted from data source 2)	(Directly copy pasted from data source 2)
10
11	DATA SOURCE 2
12	Parent Name	Parent EmaiL	Child Name (Common)
13	Name 1	test1@gmail.com	Child 1
14	Name 2	test2@gmail.com	Child 2
15	Name 3	test3@gmail.com	Child 3
16	Name 4	test4@gmail.com	Child 4
17	Name 5	test5@gmail.com	Child 5
18	Name 6	test6@gmail.com	Child 6
Sheet1

Cell Formulas
Range	Formula
F3:F8	F3	=VLOOKUP(E3,$A$3:$B$8,2,0)

Is this what you are looking for?

Book1
	A	B	C	D	E	F	G	H
1	DATA SOURCE 1				RESULT AFTER COMBINING BOTH THE SOURCES
2	Child Name (Common)	Unique Code			Child Name (Common)	Unique Code	Parent Name	Parent EmaiL
3	Child 3	359fh4			Child 1	359fh5	Name 1	test1@gmail.com
4	Child 1	359fh5			Child 2	359fh8	Name 2	test2@gmail.com
5	Child 5	359fh6			Child 3	359fh4	Name 3	test3@gmail.com
6	Child 4	359fh7			Child 4	359fh7	Name 4	test4@gmail.com
7	Child 2	359fh8			Child 5	359fh6	Name 5	test5@gmail.com
8	Child 6	359fh9			Child 6	359fh9	Name 6	test6@gmail.com
9					(Directly copy pasted from data source 2)	(vlookup applied - looking up for unique codes from data source 1 against Child name in data course 2	(Directly copy pasted from data source 2)	(Directly copy pasted from data source 2)
10
11	DATA SOURCE 2
12	Parent Name	Parent EmaiL	Child Name (Common)
13	Name 1	test1@gmail.com	Child 1
14	Name 2	test2@gmail.com	Child 2
15	Name 3	test3@gmail.com	Child 3
16	Name 4	test4@gmail.com	Child 4
17	Name 5	test5@gmail.com	Child 5
18	Name 6	test6@gmail.com	Child 6
Sheet1

Cell Formulas
Range	Formula
F3:F8	F3	=VLOOKUP(E3,$A$3:$B$8,2,0)

Thank you. This is definitely an option that could work, depending on the state of the data sources once received. I'm very grateful for your support. Thank you.

 

[Legacy 143009]

This is perfect. Thank you so very much.

Glad it did work! Thanks for the feedback