Formula Help - Performing "triple" vlookup?

Stubrok · #1 07-21-2009, 12:55 PM

I was hoping for a little help because this formula is driving me mad!

I understand vlookup returns a result after matching the left most result field in a table. But what if 3 column rules need to be met prior to returning the desired result?

I tried Match/Index and Offset (which I can't seem to understand) and even dabbled in array formulas but can't seem to get the language right (or syntax?). In any event I enclosed a sample spreadsheet detailing what I'd like to accomplish. The formula in there now is the closest I've come.

Any assistance would be much appreciated.

Morrigan · #2 07-21-2009, 01:10 PM

Try attached.

newmisslesylo · #3 07-21-2009, 01:28 PM

In cell H4 enter:

=SUMPRODUCT(($A$4:$A$15=$G4)*($B$4:$B$15=H$3)*($C$4:$C$15=$G$2),$D$4:$D$15)

Then fill down and across

Palmetto · #4 07-21-2009, 01:37 PM

Another Option: maybe use a Pivot Table? It's very flexible.

See attached.

Stubrok · #5 07-21-2009, 02:59 PM

Sumproduct.....perfect! Thanks much for the quick replies.

Question for Morrigan: What do the double dashes (--) signify in the formula you wrote?

Palmetto · #6 07-21-2009, 04:27 PM

See this link for explanation: SUMPRODUCT Double Unary

Note: xldynamics has a great white paper on this, but the site has lost its registration. Pity.

Welcome to the Excel Forum

Forum Statistics