Double XLOOKUP in Excel

Question

I have an Excel-workbook containing two sheets:

• One sheet contains the names of students with their evaluation

• And a second sheet containing the "mappings" between the criteria and the possible values with their corresponding score

So basically, I need to lookup the name of the criteria in the Students-sheet to "return" the columns of the criteria. Within these colums, I need to look for the value and return the corresponding score.

Based on my 'Students' sheet, how can I calculate the total score for each student?

Thank you very much!

Answer 1

Easiest is using named ranges:

On 'Scores'

• Select A2 through B4, name it Crit1
• Select C2 through D4, name it Crit2
• Select E2 through F4, name it Crit3

On 'Students' apply this formula on the right most column:

=XLOOKUP(C2, CHOOSECOLS(Crit1,1), CHOOSECOLS(Crit1,2)) + XLOOKUP(D2, CHOOSECOLS(Crit2,1), CHOOSECOLS(Crit2,2)) + XLOOKUP(E2, CHOOSECOLS(Crit3,1), CHOOSECOLS(Crit3,2))

Alternativly, you can do it without the named ranges:

=XLOOKUP(Students!C2, Scores!$A$2:$A$4, Scores!$B$2:$B$4) + XLOOKUP(Students!D2, Scores!$C$2:$C$4, Scores!$D$2:$D$4) + XLOOKUP(Students!E2, Scores!$E$2:$E$4, Scores!$F$2:$F$4) 

Drag down for all students.

---

EDIT: Since there's about 18 criteria, you might want to consider a slight change to your 'Scores' sheet, make it like such:

Then try this formula:

=SUM(IFERROR(INDEX(Scores!$A$2:$S$4,XMATCH(Students!C2:T2,Scores!$A$2:$A$4),TRIM(RIGHT(Students!$C$1:$T$1,2))+1),0))

Adjust range accordinly.

Answer 2

You can also use LAMBDA function:

=REDUCE(0, SEQUENCE(3), 
LAMBDA(a,b, a+ XLOOKUP(INDEX($C$2:$E$3,,b), 
INDEX(Scores!$A$2:$F$4,,2*b-1), INDEX(Scores!$A$2:$F$4,,2*b))))

This formula is easy to scale up. 3 means the number of criteria, the range C2:E3 represents student ratings, Scores!A2:F4 represents the whole "mapping".

Answer 3

Here is my proposal, using one single dynamic array formula, which will spill for the whole data in sheet Students:

---

• Formula used in cell F2

=BYROW(C2:E3,LAMBDA(α,LET(ε,Scores!A2:F4,δ,LAMBDA(φ,
  CHOOSECOLS(ε,SEQUENCE(COLUMNS(ε)/2)*2-φ)),SUM(IFERROR((α=δ(1))*δ(0),0)))))

---

You will need to increase the range as per your suit.

---