Most efficient way to get value z from string x and string y in Excel? I have a reference sheet for the values.

[ryanoh815]

Image codes: Screen Shot 2014 05 29 at 10 15 06 PM

So, this is what I have [above] (not exactly, but an example) in sheet 1 of my excel doc. I want to be able to get the value for say D3 of the above sheet from the following sheet [below] (sheet 2 of my excel doc).

Image codes: Screen Shot 2014 05 29 at 7 25 24 PM

I tried a long if-and statement, but the formula was too long. I have around 190 combinations in the equivalent of what sheet 2 is.

Thank you.

 

[Rukt]

ryanoh815,

Where you have a question mark (cell D3?), I assume you want it to return 180. If so, try the following formula in D3. You'll need update the sheet name from Sheet2 to whatever corresponds to your second screenshot, and update the three ranges to include all the necessary rows (rather than just 1 to 6 as in your screenshot).

=SUMPRODUCT(--(Sheet2!$A$1:$A$6=A1),--(Sheet2!$B$1:$B$6=C1),Sheet2!$C$1:$C$6)

Copy and paste this down for your other combinations.

Is that what you're after?

Rukt