Combining data from multiple worksheets

[gwong]

Hi,

I was wondering if I could get some help on how to combine data from multiple worksheets. Here is an example of what I am trying to accomplish:

Worksheet1 contains the following data:
[TABLE="width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Colour[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Kevin[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Jen[/TD]
[TD]Pink[/TD]
[/TR]
</tbody>[/TABLE]


Worksheet2:
[TABLE="width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Fruit[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Kevin[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Christine[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]Jen[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Grapes[/TD]
[/TR]
</tbody>[/TABLE]


I would like to grab the fruit data from Worksheet2 and add it to a new column in Worksheet1 for only the names specified in Worksheet1. Basically the logic I am trying to get at is, search through the Name column in Worksheet2 for the same names as in Worksheet1, then add the corresponding fruit values to Worksheet1. What kind of formula could I use for this?

Correct output should be:
[TABLE="width: 192"]
<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Colour[/TD]
[TD="width: 64"]Fruit[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Blue[/TD]
[TD]Grapes[/TD]
[/TR]
[TR]
[TD]Kevin[/TD]
[TD]Green[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]Red[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Jen[/TD]
[TD]Pink[/TD]
[TD]Orange[/TD]
[/TR]
</tbody>[/TABLE]


This is of course an extremely simplified example, the real data that I am using contains thousands of lines, but the idea is still the same. So my question is, what formula could I use for column C in Worksheet1 that can compare the name values in Worksheet1 with the name values in Worksheet2, and if they are the same, grab the fruit value corresponding to that name and add it to the column C? Any help would be greatly appreciated it.

Thank you,
Gavin

 

[Stan_C]

If your values a unique...

Sheet1

*	A	B	C
Name	Colour	Fruit
John	Blue	Grapes
Kevin	Green	Banana
Anne	Red	Apple
Jen	Pink	Orange

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =VLOOKUP(A2,Sheet2!$A$2:$B$8,2,FALSE) <tbody> </tbody>

<tbody>
</tbody>

Sheet2

*	A	B
Name	Fruit
Jack	Apple
Kevin	Banana
Anne	Apple
Richard	Orange
Christine	Kiwi
Jen	Orange
John	Grapes

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4