Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best way to create a formula for 3 different matches.
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.
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best way to create a formula for 3 different matches.
Krish wrote:
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 D2:D9999 Percentage-to-load lookup is desired in E2:E9999 Percentage-to-load is actually in Sheet2!D2:D9999 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If value matches criteria, return formula | Excel Worksheet Functions | |||
Is there any way to use HLOOKUP in an array formula to return multiple matches? | Excel Worksheet Functions | |||
hi - is there a formula that matches text in different columns? | Excel Worksheet Functions | |||
create a scale x axis that matches the straighted form of a norma. | Charts and Charting in Excel | |||
Use of the DSUM formula to find exact matches in datatables | Excel Worksheet Functions |