Thread: search a unique value determined by 2 variables

Hi!

I'm new to the forum. Hope somebody can help me.
I have a workbook with 2 sheets. In sheet 2 I have in column A a list of territories, in column B a list of lengths and in column C the corresponding price for that pair of territory & length.
Sample:
Territory Length Price
1 15 $600
1 16 $650
1 17 $700
2 15 $675
2 16 $700

In sheet 1 I have fields containing the territory & length. For every row in sheet 1 I need to find the corresponding pair of territory & length in sheet 2 and to copy the corresponding price from sheet2 column C in the last column in sheet1.
I think I should use a VLOOKUP but I'm not sure how.
Thanks for any idea!

Hi

The SUMPRODUCT function should get you there.

=sumproduct(--(sheet2!A1:A10=territory),--(sheet2!B1:B10=length),(sheet2!C1:C10))

Adjust the sheet ranges to suit and replace territory and length with the cell references from sheet1.

rylo

Sorry, it doesn't do it because I don't need to perform any operation. Maybe if I give you a sample of Sheet 1 it make it easier to understand. Values in Sheet 1 look like this:
Territory Length Price
1 17 to determine from sheet 2
1 15 to determine from sheet 2
1 15 to determine from sheet 2
2 20 to determine from sheet 2
2 19 to determine from sheet 2
2 19 to determine from sheet 2
2 19 to determine from sheet 2

So, I need to find in sheet 2 the pair of territory/ length from sheet 1 and to write the price from sheet 2 in sheet 1.
Thanks!