SUMPRODUCT(VLOOKUP(B10:B13,...)) does not work

[joeu2004]

The following results in a #VALUE error, even when array-entered, which should not be necessary:

=SUMPRODUCT(C10:C13,VLOOKUP(B10:B13,A4:B6,2,0))

The intended interpretation is:

C10*VLOOKUP(B10,A4:B6,2,0) + C11*VLOOKUP(B11,A4:B6,2,0) + ....

The following work-around does not produce the correct result (array-entered):

=SUM(C10:C13*VLOOKUP(B10:B13,A4:B6,2,0))

It is misinterpreted as (array-entered):

=SUM(C10:C13*VLOOKUP(B10,A4:B6,2,0))

In other words, the VLOOKUP expression returns a single value instead of an array.

But the following does work as intended [1]:

=SUMPRODUCT(MATCH(B10:B13,A4:A6,0))

It is correctly interpreted as:

MATCH(B10,...) + MATCH(B11,...) + ....

My work-around is to calculate each VLOOKUP(B10,...), VLOOKUP(B11,...) etc in G10:G13 ("helper cells") and replace the original formula with:

=SUMPRODUCT(C10:C13,G10:G13)

Can someone offer a work-around that does not require helper cells?


-----
[1] PS, more to the point, the following works:

=SUMPRODUCT(C10:C13,MATCH(B10:B13,A4:A6,0))

It is correctly interpreted as:

C10*MATCH(B10,A4:A6,0)) + C11*MATCH(B11,A4:A6,0)) + ....

 

[joeu2004]

I forgot to include example data.

A4:A6 contains "high", "med", "low".

B4:B6 contains 1.2, 1, 0.8.

B10:B13 contains "high", "high", "med", "low".

C10:C13 contains 3, 5, 2, 5.

And I meant to say: any work-around must work in Excel 2010, and preferably in Excel 2003.

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUMIF(A4:A6,B10:B13,B4:B6)*C10:C13)

 

[joeu2004]

@Aladin.... Or simply:

=SUMPRODUCT(C10:C13, SUMIF(A4:A6,B10:B13,B4:B6))

Works great! Thanks.

 

[Aladin Akyurek]

@Aladin.... Or simply:

=SUMPRODUCT(C10:C13, SUMIF(A4:A6,B10:B13,B4:B6))

Works great! Thanks.

You are welcome. Pleased to see that you opt for the comma syntax.