Best way to create a formula for 3 different matches.

[Krish]

I have a spreadsheet pulling data relating to pricing from MS Query from 2 different sources into 2 different worksheets. Both worksheets have common fields Product ID, Unit of Measure, effective date. The first worksheet has Product Base Cost. The second worksheet has Percentage to load the cost. I am looking for a statement so that the percentage data when all the 3 (Product ID, UOM and Effective date) criteria's are met, is brought to first worksheet to allow me to determine the Product Loaded cost. I understand that VLookup is too simple for this purpose. Any suggestion is appreciated very much.

 

[smartin]

I have a spreadsheet pulling data relating to pricing from MS Query from
2 different sources into 2 different worksheets. Both worksheets have
common fields Product ID, Unit of Measure, effective date. The first
worksheet has Product Base Cost. The second worksheet has Percentage to
load the cost. I am looking for a statement so that the percentage data
when all the 3 (Product ID, UOM and Effective date) criteria's are met,
is brought to first worksheet to allow me to determine the Product
Loaded cost. I understand that VLookup is too simple for this purpose.
Any suggestion is appreciated very much.

Suppose you have 9998 rows of data in each worksheet, and the following
in the worksheet Sheet1 where you want the result:
PID in A2:A9999
OUM in B2:B9999
EFF in C2:C9999
PBC in D29999
Percentage-to-load lookup is desired in E2:E9999

Percentage-to-load is actually in Sheet2!D29999

The formula for Sheet1!E2, which can be filled down, is:
=SUMPRODUCT((A2=Sheet2!$A$2:$A$9999)*(B2=Sheet2!$B$2:$B$9999)*(C2=Sheet2!$C$2:$C$9999)*Sheet2!$D$2:$D$9999)