Combining data from worksheets - lookup?

[Guest]

I have 2 separate worksheets that I am attempting to combine into 1.

I cannot do a Vlookup based on an item #, as it could be in the sheet
multiple times and I need to match each transaction. The combination of item
#, date, location, and quantity should be unique. How do I search
spreadsheet #2 to return data to sheet #1 when the criteria above is met?

 

[Earl Kiosterud]

Connie,

Presumably, you're trying to combine the rows, a row from sheet1 with row
from sheet2, matching on item number, making one longer row. You don't say
which sheet has the multiple matches. Let's say it's sheet2. Then there
must be only one match in sheet1 for any item number. In that case, do your
VLOOKUP in sheet 2. It will find the matching record in sheet 1. If this
isn't the case, you may need to describe your setup, and what you're trying
to do. Give an example.

 

[Guest]

Earl,

Both sheets have multiple matches. What I would like to do is take sheet 1
and be able to pull the cost column from sheet 2 into it.

Below is an example of what I am working with.

Sheet 1

Item # Date Location Qty Cost
100 8/1/05 53 100 10
100 8/1/05 96 50 11
200 8/1/05 53 75 15
200 8/2/05 96 100 16
300 8/2/05 53 100 20

Sheet 2

Item # Date Location Qty Cost
100 8/1/05 53 100 10
100 8/1/05 96 50 12
200 8/1/05 53 75 16
200 8/2/05 96 100 16


Connie,

Presumably, you're trying to combine the rows, a row from sheet1 with row

 

[Earl Kiosterud]

Connie,

Still not sure I understand. Wild guess: Taking Item 100 -- should the
first one in sheet 1 be replaced by the first item 100 from sheet2, then the
second item 100 should be replaced by the second item 100 from sheet2? I'm
not sure why I'm asking, because short of a macro written to this
requirement, nothing comes to mind.