Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index and match help
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index and match help
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Tom" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index and match help
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). -- Biff Microsoft Excel MVP "Bernard Liengme" wrote in message ... 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Tom" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |