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!

Attached Files

Price Calculator.xlsx (16.9 KB, 11 views)

This seems to work =SUMPRODUCT(--([@Material]=Prices[Material])*([@Density]=Prices[Density])*Prices[Cost])*[@BF]
Don't ask me why the other doesn't

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I suspect the comma does not coerce TRUE/FALSE to 1/0 where the * operator does

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Ahhhh...I'm forever switching between the two and never knew why any particular combination worked and another didn't.

Good karma for you, mate! Thanks!

Perhaps this link can shed some light

The basic idea is that arithmetic operators automatically coerce

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post