Index and Match to find a row header that aligns with variable in specific column

Question

I have been trying to solve this issue, but I need help:

I want to lookup a percentile that matches a certain test score of a student in a certain grade, in a score table.

So the known variables are GRADE LEVEL (Column headers Kinder-gr11), and TEST SCORE.

• step 1: lookup the grade level of a certain student,
• step 2: lookup the test score of this student,
• step 3: result is the matching percentile score (row header).

I cannot get this to work! So frustrating. Here is a sample Google Sheet: Click Here

Does anyone have ideas?

Answer 1

try:

=ARRAYFORMULA(INDEX(IFERROR(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(
 IF(HLOOKUP(S3:S7, B2:M, TRANSPOSE(ROW(A2:A)), 0)=U3:U7, 
 TRANSPOSE(N3:N),)),,999^99)), " "), "no match"),,1))

Answer 2

Solved with help from another forum:

=iferror(vlookup(U3,{filter($B$2:$M,$B$2:$M$2=S3),$A$2:$A},2, true))

nb. the table is from the NWEA standardized test scores for MAP testing. I did not make the table, and I know that there are some of the same scores for various percentiles (depending on how students score sub items of the test which is beyond the scope of my question)

Hans