Hello All!
I'm trying to run an XLOOKUP to create a grade level summary of several different gradebooks based on the student and the subject and convert the values.
A few things need to happen:
1. Pull scores by student for the appropriate subject.
-The subjects will actually be standards and there will be hundreds, so typing the actual subject is not an option.
- It will have to pull from multiple gradebooks. (We won't know if John is in Mrs. Johnson's class or Mr. Smith's class, so it will have to look for him in both classes.)
2. Convert the score bands (listed under the blue) to the corresponding number in the summary.
3. Leave alternating columns untouched.
I was thinking something like this might work in K7 for "Joe" but it isn't. IFERROR(LOOKUP(IF(D2:H2=K2,XLOOKUP(J7,$d3:D4,E3:H4,if(D10:H10=K2,XLOOKUP(J7,$d11:D13,E11:H13)))),{"0-9","10-19","20-29","30-39","40-49","50-59","60-69","70-79","80-89","90-100",""},{-1,-1,-1,-1,-1,-1,0,0,1,1),"")
*I am open to the idea of a Query in place of a formula, but I am a Query novice. I'm also not sure if the fact that every other column cannot be changed will make a Query impossible.
Thanks in advance!
I'm trying to run an XLOOKUP to create a grade level summary of several different gradebooks based on the student and the subject and convert the values.
A few things need to happen:
1. Pull scores by student for the appropriate subject.
-The subjects will actually be standards and there will be hundreds, so typing the actual subject is not an option.
- It will have to pull from multiple gradebooks. (We won't know if John is in Mrs. Johnson's class or Mr. Smith's class, so it will have to look for him in both classes.)
2. Convert the score bands (listed under the blue) to the corresponding number in the summary.
3. Leave alternating columns untouched.
I was thinking something like this might work in K7 for "Joe" but it isn't. IFERROR(LOOKUP(IF(D2:H2=K2,XLOOKUP(J7,$d3:D4,E3:H4,if(D10:H10=K2,XLOOKUP(J7,$d11:D13,E11:H13)))),{"0-9","10-19","20-29","30-39","40-49","50-59","60-69","70-79","80-89","90-100",""},{-1,-1,-1,-1,-1,-1,0,0,1,1),"")
*I am open to the idea of a Query in place of a formula, but I am a Query novice. I'm also not sure if the fact that every other column cannot be changed will make a Query impossible.
Thanks in advance!