Find a value from the first column - 2 values, 1 is a number range (EX: 19-20)

Thank you to all for your support as I'm relearning (and learning new) Excel. I've been able to complete everything on the attached sample file except for F2:F15. Really, I just need the formula for F2 (Subtest SC) and I will figure out the rest (good learning experience for me).

In short, F2:F15 are found by using the values from C2 (Age is the same for each Subtest) and E (Raw Score, which is entered manually).

The formula is I tried is in J2 (I didn't expect it to work though because of the number ranges like SC!B9). Just to test my formula I extended the SC worksheet so no number ranges existed (SC_Long). J3 - Same formula but pointing to the SC_Long sheet. If necessary (or easier) I can extend each sheet with number ranges in cells just like I did for SC.

Once again, any assistance is greatly appreciated.

Tom

Thanks for the quick response, Dave. I tried it but that didn't do the trick. I thought that the value at the beginning of the formula is what you're supposed to be returning? In this case I used, =INDEX(SC_long[Score] .

I'm trying to find the Score (A:A) value by looking up the Age (From C2 to tell Excel to look at which of the Headers on SC_long sheet) and the Raw Score (E2, in this case).

=INDEX(SC_long[Score],MATCH(C2,SC_long[#Headers],0),MATCH(E2,SC_long[[5.0]:[8.0]],0))

Essentially: Find the Score of a child that is 5.0 years old and has a Raw Score of 24. The array (?) should find B6 (5.0 and 24) and return the value from A6 (15). If the child was 6.0 with a Raw Score of 24, it would find D9 and return the score from A9.