Trying to align/merge two data sets/tables

[Sprackers]

Ive been working on trying to do this for 4 days and I still can't do it. gone through every tutorial everywhere.

Trying to do this


List 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]ID258[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]ID236[/TD]
[/TR]
</tbody>[/TABLE]

List 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]ColumnB[/TD]
[/TR]
[TR]
[TD]John [/TD]
[TD]Carrots[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Apples[/TD]
[/TR]
</tbody>[/TABLE]

To this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column c[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]ID 258[/TD]
[TD]Carrots[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]ID 236[/TD]
[TD]Apples[/TD]
[/TR]
</tbody>[/TABLE]


This is VERY simplistic example which is good.

List 1 is 1500 names and IDs
List 2 is 375 with vegetables and fruits and sometimes both.

I have 1500 names in no specific order so its not as simple as sort in alphabetical order and they will all be opposite each other.

Any help apprreciated

Sprackers

 

[mart37]

Use VLOOKUP or INDEX and MATCH

 

[Sprackers]

Hi MArt

Firstly thanks for comng back, Thats what I was trying to say ive studied Vlookup and index match and niether work. the examples people show me assume I am already a excel wizard. Which clearly I am not.

thats why I made the example simple so someone could perhaps write the formula and I could see how to do it.

REgards

Phil

 

[mart37]

Excel Workbook
	A	B	C	D	E
1	List 1			List 2
2	Name	ID		Name	F/V
3	John	ID258		John	Carrots
4	Peter	ID236		Peter	Apples
5
6
7	Name	ID	F/V	ID	F/V
8	John	ID258	Carrots	ID258	Carrots
9	Peter	ID236	Apples	ID236	Apples
Sheet

 

[Sprackers]

YES....its works...Brilliant

Thanks Mart a life saver.

What no one told me was that you needed all 4 formulas for it to work. I was led to believe that one single formula in one single cell would do it all.


No wonder I couldn't figure it out.


Thanks again and and with the example so simple I am able to go through it and understand whats happening.....a bit.

Cheers

Sprackers

 

[mart37]

Not 4 but 2 formulas are enough.
I give two examples: one with 2 VLOOKUP (B8 and C8) and one with 2 INDEX/MATCH (D8 and E8) solutions.
You can choose from VLOOK or INDEX/MATCH.