Select value from table based on two criteria
OK...total brain meltdown here. I need to find the value in the third column of a table based on the values in the first two. It's basically a small lookup table with four unique rows.
So I did this in Google docs using Sumproduct but when I exported it to Excel, something is amiss. I've done it this way before in Excel, but I can't seem to figure out where I've gone wrong.
If someone could lead the way or point me to another solution, I'd appreciate it.
In the attached workbook, the lookup table, Prices, is in the tab named Metrics. There are three columns: Density, Material, and Cost. As you can see, there are just two different densities and materials an, for each combination a unique cost, and thus a total of four rows. The test row I am using (density=14 and material=E) is highlighted.
Highlighted on the first tab, Summary, is the cell with the formula I am having fits with and the other cells in the table that it refers to. Basically, I want it to find the cost in the Prices table using the density and material of the SKU for that row. The formula as is:
=SUMPRODUCT([@Material]=Prices[Material],[@Density]=Prices[Density],Prices[Cost])*[@BF]
So it should return cost for the criteria in that row (density=14 and material=E) and multiply that number by the value for BF (i.e. board feet).
=Cost(14,E)*BF
=0.2968*7906
=2346.50
As you can see, the formula returns a big fat zero. Sorry for the long explanation. I'm sure it's a simple solution for one of you smart folk. Cheers!
|