Search for a value that repeats several times in another table

[RobertoB]

Hi, i have a table that looks as follows:

CUSTID CUSTNAME ADDRESS
123 customer1 The street
345 customer2 The road


And I have another table that looks as follows

CUSTID PRODUCT VOLUME
123 z-Units 13
123 a-units 25
345 z-units 10
345 b-units 5
123 b-units 12


And I need and output table that loos as follows:

CUSTID CUSTNAME ADDRESS PRODUCT VOLUME
123 customer1 The street z-units 13
123 customer1 The street a-units 25
123 customer1 The street b-units 12
345 customer2 The road z-units 10
345 customer2 The road b-units 5


I have try to do this with VLOOKUP, and also with a combination of OFFSET
and MATCH, but I always get an N/A. As tbale 1 and table 2 can have variable
lenghts, I wonder what can I do.

 

[ShaneDevenshire]

Hi,

1. Copy your second table to A1 of a new sheet and insert 2 columns between
B:C
2. In cell B2 enter the formula
=VLOOKUP($A2,SourceSheet!$A$2:$C$3,2,FALSE)
Copy this formula down.
Copy the first formula over to C2 and change the 2 to a 3
=VLOOKUP($A9,SourceSheet$A$2:$C$3,3,FALSE)
and copy it down.

This assumes that the SourceSheet is where your first table is and A2:C3
contains the data.

If this helps, please click the Yes button.

 

[Pete_UK]

Assuming these tables are on different sheets in the same workbook, first of
all insert new columns B and C in the second sheet/table and enter CUSTNAME
in B1 and ADDRESS in C1. Then put this formula in B2:

=VLOOKUP($A2,Sheet1!$A:$C,COLUMN(B1),0)

Copy it into C2, and then copy B2:C2 down for as many rows as you need.

Hope this helps.

Pete

 

[ShaneDevenshire]

Hi,

I did not mention that you can't build the entire table off of the first
column using VLOOKUP because there are repeat entries.

As a side - when you say you used a formula and it didn't work, it's often
best to show us the formula.