Index and match help

[Tom]

I have a workbook that includes the following sheets; Grades, reading, math
comp, applied math, language and combined math. The combined math sheet is a
table and it contains the scores for math computation in cells b2: aa28 where
the scores are listed as 0 to 25 correct in increments of 1. The combined
math sheet also lists the scores for applied math in cells a3:a28 listed in
increments of 1 from 0 to 25.

To look up the grade level of a combined math test you find the number
correct in the math computation score from b2:aa28 and then match it against
the applied math score found in a3:a28 . The intersection of that row and
column returns a grade level equivalent.

I have the math computation score in cell d2 of the sheet called grades and
I have the applied math score in cell E2 of the grades worksheet. I need a
function to lookup and return the score to cell I 2 on the grades work
sheet.

i think I need an index and match function :
I can't seem to get the syntax correct .

I have

=index(combined math!$a$2:$aa$28,match(Grades!$d$2,combined
math!$a$2:4aa$28,),match(Grades!$e$2,combined math!$a$2:$a$28,))


can you help? I would really appreciate it

Thanks
Tom

 

[Bernard Liengme]

A quick look at your message reveals a simple error: when you reference a
worksheet whose name contains a space, it must be enclosed in single quotes.

=index('combined math'!$a$2:$aa$28,match(Grades!$d$2,'combined
math'!$a$2:4aa$28,),match(Grades!$e$2,'combined math'!$a$2:$a$28,))

I prefer to use names like CombinedMath with no spaces

If this does not work, please give us a shorter version of the problem.
best wishes

 

[T. Valko]

There's also a problem with the MATCH functions. It looks like they're
backwards and the first MATCH lookup_array is referencing a 2 dimensional
array. The lookup_array must be a 1 dimensional array (single row or single
column).