Matching Two Columns From One Worksheet to Two Columns in Another While Returning a Value

[PeanutB7]

I am working with one Spreadsheet that contains two worksheets. I am attempting to match two columns in spreadsheet one to two columns in spreadsheet two. A respective number would then be inserted as the output from Column C

Spreadsheet 1
Column A Column B
Location Item Number

Spreadsheet 2
Column A Column B Column C
Location Item Number Activity Qty


I have tried a few formulas but no success.

=SUMPRODUCT(--($A$2:$A$2500='Spreadsheet 2'!$A$2:$A$2500), (--$B$2:$B$2500='Spreadsheet 2'!$B$2:$B$2500), --('Spreadsheet 2'!C2))

Any help would be GREATLY appreciated.

Thanks in advance,

J

 

[Gruuuu]

Just use a helper column off to the side. Concatenate your lookup column values together (like so)
[tt]
=A2&"|"&B2
[/tt]
(I always put a pipe in as a separator. You don't have to use a pipe, but you should definitely use something)

Then when you do your lookup, concatenate your lookup_value (like so):
[tt]
=VLOOKUP(A2&"|"&B2,Sheet1!$A$1:$D$5000,4,false)
[/tt]

If you choose to do this, you would need to put your helper column in column A, because of the particularly annoying limitation of VLOOKUP that your lookup index must be to the left of your desired value.

If you wish to avoid that limitation, you would need to use INDEX/MATCH, which is gone into some detail here

 

[SkipVought]

This does not make sense as a lookup!

So you do a lookup -- Then what?

What you supposedly tried via SUMPRODUCT() is not a lookup, but an aggregation.

Suppose you tell us WHAT it is that you need to do, rather than HOW you think it ought to be achieved.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gruuuu]

Oh wait, I believe I've misunderstood the question. PeanutB7, do you just want a count of occurring combinations of Location and Item Number?

 

[PeanutB7]

Folks

Thanks for the help.

Worksheet 1 contains a Column A for location and Column B for a product item number (i.e., SKU). Worksheet 2 has the same two columns as well as an activity quantity Column C showing how many of the item numbers have been used in each location. I would like to match each row of worksheet 1 in respect to the location and item number with the corresponding location and item number in worksheet 2. I would then like to transfer the value in worksheet 2 into worksheet 1 based on the match of Column A & B in each specific row. We are trying to review par levels of inventory (from Worksheet 1) in each location based on the activity that is indicated in worksheet 2. Based on some research online I came up with the SUMPRODUCT formula but that may not be best formula to use.

Worksheet 1
Column A Column B Column C
Location Item Number Activity Qty
Pittsburgh 325-11-2222 16 (Value determined by formula looking in worksheet 2)
Cleveland 325-11-2222 12 (Value determined by formula looking in worksheet 2)


Worksheet 2
Column A Column B Column C
Location Item Number Activity Qty
Cleveland 325-11-2222 12
Pittsburgh 325-11-1111 10
Pittsburgh 325-11-2222 16
Toledo 325-22-1234 18

Again I appreciate the assistance and hope I have provided enough information to help.

Thank you kindly,

J

 

[SkipVought]

paste in C2 and copy/paste down...
[tt]
=SUMPRODUCT((A2='Spreadsheet 2'!$A$2:$A$2500)*(B2='Spreadsheet 2'!$B$2:$B$2500)*('Spreadsheet 2'!$C$2:$C$2500))

[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PeanutB7]

Is there anyone that can provide advise on this formula?

Thank you and have great day,

J

 

[PeanutB7]

Sorry Skip - I did not see your earlier post.

Thank you very kindly,

J

 

[SkipVought]

P, it is VERY puzzeling, as your question is so ambiguous.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, the SUMPRODUCT() function as posted, is an aggregation. So I there is more than one row occurrence of the column A/column B pair in Worksheet 2, the column C values will be SUMMED.

Secondly, it is a poor practice to have a table of data without column headings. Excel is designed to work with TABLES.

faq68-5184

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]