Excel RANGE VLOOKUP-CodePudding

STUDENT TIME    SCORE   WANT
JOHN    1   68  146
JOHN    2   78  146
JOHN    3   77  146
JOHN    4   91  146
JOHN    5   96  146
JAMES   1   66  119
JAMES   2   53  119
JAMES   3   80  119
JAMES   4   96  119
JAMES   5   50  119
JAMES   6   94  119

I have data COLUMNS 'STUDENT' AND 'TIME' AND 'SCORE' and wish to create 'WANT' and the rule which for I will need VLOOKUP is this: WANT = the sum of the SCORE values at TIMES 1 and 2, so I WISH TO USE VLOOKUP to find the 'SCORE' values for each 'STUDENT' at TIMES 1 and 2 and take the sum.

CodePudding user response：

Assuming your dataset is ordered by "student name" (with unique student names), then "time", you could use :

Classical way, in F2:

=IF(AND(B2=1,B3=2,A2=A3),C2 C3,IF(AND(B2=2,B1=1,A2=A1),C2 C1,OFFSET($F$1,MATCH(A2,A$2:A2,0),0)))

Greedy way (Office365 needed), in F2 :

=VLOOKUP(A2,FILTER($A$2:$C$12,$B$2:$B$12=1),3,FALSE) VLOOKUP(A2,FILTER($A$2:$C$12,$B$2:$B$12=2),3,FALSE)

Reference :

CodePudding user response：

You can try SUMIFS() in this way.

=SUM(SUMIFS($C$2:$C$12,$B$2:$B$12,{1,2},$A$2:$A$12,A2))

It may need to array entry for older versions of excel. Array entry by CTRL SHIFT ENTER.