how do I use array references in VLOOKUP()?

[tsimkus]

I have several rows in spreadsheet_1, each row with an employee name followed
by four columns of numbers ranging in value from 1 to 7. Following these
numbers is a SCORE I want to calculate, and it is in this column that I want
to place the formula I am asking about.

NAME CAT1 CAT2 CAT3 CAT4
Joe Smith 5 4 5 6 sum_of_values
Harry Houdini 4 4 3 3 sum_of_values
Jane Doe 1 2 3 4 sum_of_values

In a second spreadsheet, spreadsheet_2, I have a 7 x 4 array of values whose
rows correspond to the CAT score in the first spreadsheet, and whose columns
correspond to the value associated with that CAT score.

SCORE CAT1 CAT2 CAT3 CAT4
1 5 7 2 10
2 10 14 4 20
3 15 21 6 30
4 20 28 8 40
5 25 35 10 50
6 30 42 12 60
7 35 49 14 70

I want the formula in the cells labeled "sum_of_values" in spreadsheet_1 to
use the CAT scores in the row for a person to look up the asociated value for
a score and CAT column in spreadsheet_2, do that for each score and value,
and add the result.

For example, for Joe Smith his "sum_of_values" would be:
CAT1 score = 5, associated value = 25
CAT2 score = 4, associated value = 28
CAT3 score = 5, associated value = 10
CAT4 score = 6, associated value = 60

sum_of_values = 123

I tried using
SUM(VLOOKUP(spreadsheet_1!b2:e2,spreadsheet_2!a2:e8,{2,3,4,5}), and entering
this as an array formula (CTRL-SHIFT-ENTER), but the VLOOKUP() function only
evaluates the first argument once, as a "5", and uses that value for all the
subsequent lookups corresponding to the array constant "{2,3,4,5}".
Therefore, I get an array summed value, but it calculates as if Joe Smith
scored all "5"s rather than "5 4 5 6".

 

[T. Valko]

Try this:

Entered in F2 of sheet1:

=SUMPRODUCT((Sheet2!A$2:A$8=B2:E2)*(B$1:E$1=Sheet2!B$1:E$1)*Sheet2!B$2:E$8)

Copy down as needed.

 

[Lori]

I think this should be enough,

=SUMPRODUCT(--(Sheet2!A$2:A$8=B2:E2),Sheet2!B$2:E$8)

Trying =SUM(VLOOKUP(b2:e2,sheet2!a2:e8,{2,3,4,5})) shows the inconsistency
in evaluating some array formulas:
- If it's array-entered in one cell it returns 120 - the result of fixing
the first argument to B2 and using {2,3,4,5}.
- If it's array entered in more than one cell it gives 100 - the result of
using B2:E2 but fixing the third argument to {2}.