"Multiple joins" in Excel with Vlookup

[bluerog]

I have table column with "Customer Group", "Item", and "Price"

I have another table that pulls (Customer Group & Item) with a vlookup to get price from this table into another one.

I concatenate information in a column, and then look up the concatenated value with this:
=VLOOKUP(B12&E12,I:L,4,0)

Is there a better way?

Thank you much,

Roger!

[NBVC]

As long as there is one unique combination for each... then you can use this formula:

=SUMPRODUCT(--(J:J=B10),--(K:K=E10),L:L)

[martindwilson]

or
=INDEX($L$2:$L$27, MATCH(B10 & E10, INDEX($J$2:$J$7 & $K$2:$K$7, 0), 0))

[bluerog]

Thank you both. These work great and I don't have to add the dumb concatenated column.

I am going to pick the brains of NBVC someday and figure out what the "--" really does in Excel. But I need the explanation simplified if possible.

Somehow you've taken the "SUMPRODUCT" function and made it useful for things other than...well... summing and multiplying.

You all rule.

Roger!

[NBVC]

The "--" is usually called double negative or double unary... it serves to coerce arrays of TRUE/FALSE resulting from the individual conditional checks to 1/0 arrays so that SUMPRODUCT() can do it's math as designed...

There was a good link at Xldynamic.com that explained it all well, but no longer accessible...

Many examples exist on the net though if you google Excel and Sumproduct or similar...