H/Vlookup the last match and display value

Question

I am using a Google Sheet to record grades. Grading Sheet

I have 2 sheets.

• Rubric Marking will be where I add criteria codes and give students a score 1-4 based on their achievement of that criteria.
• Criteria will be where I list all criteria codes for the course and I want to receive the last score the student receives for each criteria code.

The goal is to record the LAST score for each criteria for each student.

I started with HLOOKUP and it seems to do what I need but the last option relating to sorted and not sorted lists don't cover what I am trying to achieve. I've tried various examples with LOOKUP, MATCH and INDEX but to no avail.

Any help would be greatly appreciated.

Answer 1

try:

=ARRAYFORMULA(IFNA(HLOOKUP(E2:2, QUERY(""&TRANSPOSE(SORT(TRANSPOSE({
 COLUMN(RubricMarking!E2:2); RubricMarking!E2:2; RubricMarking!E4:100}), 1, 0)), 
 "offset 1", 0), MATCH(C4:C, RubricMarking!C4:C, 0)+1, 0)))