Return cell value based on criteria of 2 columns

**Fatnslow** · 07-03-2012, 04:33 AM

Hi all,
I've searched, researched and scratched my brain on this and I can't seem to get this formula to work. I'm using nesting sumproduct formula, but this is not working.

What I'm trying to do is populate a table in one worksheet, using data from another worksheet....the catch - The formula must look up 2 values in columns A and B and return the value in column C.

Attached is the sample of what I'm trying to do, and a sample of the formula I'm trying to use.

All help would be greatly appreciated.
Cheers,
Fatnslow

**zbor** · 07-03-2012, 04:40 AM

Almost there Fatnslow:

Formula:

Please Login or Register  to view this content.

Another way would be Pivot table:

**Fotis1991** · 07-03-2012, 04:41 AM

Hi. try

=SUMPRODUCT(($A$2:$A$13=A22)*($B$2:$B$13=B22)*($C$2:C13))

**Spencer101** · 07-03-2012, 04:42 AM

Try this formula in cell C22 and copy down - =SUMPRODUCT(--($A$2:A$13=A22)*(B$2:B$13=B22),C$2:C$13)

You will also need to fill in A22-A25 with Australia and A27-A29 with US etc. in order for this to work.

Is that what you meant?