multiplying an array by a VLOOKUP value

[jeffnunner]

I'm a little rusty on arrays. I'd like cell H2 to look at cells $C2:$F2, and for each cell, multiply by the value that the name in cells $C$1:$F$1 represents, looking up based on the values in the Compensation tab, then sum the products.

Thanks in advance for your help!

[AZ-XL]

Hi
h2 cell =SUM(C2:F2*TRANSPOSE(Compensation!$B$2:$B$5)) Ctrl+Shift+enter

Sorting of of column names have to be same with Names in Compensation worksheet

[jeffnunner]

That didn't work for me, but this did:

=SUMPRODUCT(C2:F2,TRANSPOSE(Compensation!$B$2:$B$5))

Thanks!

[Tony Valko]

If you can sort the data on the Compensation sheet by column A in ascending order then you can use this formula:

=SUMPRODUCT(LOOKUP(C$1:F$1,Compensation!A$2:B$5)*C2:F2)

Copy down as needed.