Suppose Sheet1 looks like this:

KEY|VALUE
100 | Chevy
200 | Honda

Suppose Sheet2 looks like this:

KEY|VALUE
100 | Corvair
100 | Corvette
100 | Impala
200 | Accord
200 | Civic

How do I create Sheet3?

100|Chevy|Corvair
100|Chevy|Corvette
100|Chevy|Impala
200|Honda|Accord
200|Honda|Civic

Thanks.

You can actually just insert a new column B in Sheet2--or copy sheet2 to sheet3
and do the work there.

In B1, put this formula:
=vlookup(a1,sheet1!a:b,2,false)

and drag down the column.



wrote:

Suppose Sheet1 looks like this:

KEY|VALUE
100 | Chevy
200 | Honda

Suppose Sheet2 looks like this:

KEY|VALUE
100 | Corvair
100 | Corvette
100 | Impala
200 | Accord
200 | Civic

How do I create Sheet3?

100|Chevy|Corvair
100|Chevy|Corvette
100|Chevy|Impala
200|Honda|Accord
200|Honda|Civic

Thanks.

--

Dave Peterson

In Sheet 2, Column C I add the formula:

=VLOOKUP(A1,sheet1:A:B,1,FALSE)

This results in #NAME?

Is there a solution that doesn't require drag-filling?

Hey

or you could go the long way round...

Option Explicit
Dim MyCell1, MyRng1 As Range
Dim MyCell2, MyRng2 As Range
Dim MyCell3, MyRng3 As Range
Dim MyStart, MyEnd1, MyEnd2, MyEnd3 As String

Private Sub CommandButton1_Click()
MyStart = "A1"
Sheets(2).Select
[A1].End(xlDown).Select
MyEnd1 = ActiveCell.Address
Range(MyStart, MyEnd1).Copy Sheets(3).[A1]
Set MyRng1 = Range(MyStart, MyEnd1)


Sheets(3).Select
[A1].End(xlDown).Select
MyEnd3 = ActiveCell.Address
Set MyRng3 = Range(MyStart, MyEnd3)
Sheets(1).Select
[A1].End(xlDown).Select
MyEnd2 = ActiveCell.Address
Set MyRng2 = Range(MyStart, MyEnd2)

For Each MyCell2 In MyRng2
For Each MyCell3 In MyRng3
If MyCell3 = MyCell2 Then
MyCell3.Offset(0, 1).Value = MyCell2.Offset(0, 1).Value
End If
Next MyCell3
Next MyCell2
Sheets(3).Select
[A1].Select
For Each MyCell1 In MyRng1
If ActiveCell = MyCell1 Then
ActiveCell.Offset(0, 2).Value = MyCell1.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select
End If
Next MyCell1
End Sub

S

wrote:
In Sheet 2, Column C I add the formula:

=VLOOKUP(A1,sheet1:A:B,1,FALSE)

This results in #NAME?

Is there a solution that doesn't require drag-filling?

Watch your typing <bg.

=VLOOKUP(A1,sheet1:A:B,1,FALSE)
should be
=VLOOKUP(A1,sheet1!A:B,2,FALSE)
or
=VLOOKUP(A1,'sheet 1'!A:B,2,FALSE)
(single quotes maybe required depending on the name of that sheet)

And you want that 3rd parm to be 2--you're bringing back the value in the second
column in that range (A:B on sheet1).

wrote:

In Sheet 2, Column C I add the formula:

=VLOOKUP(A1,sheet1:A:B,1,FALSE)

This results in #NAME?

Is there a solution that doesn't require drag-filling?

--

Dave Peterson