Lookups with vertical and horizontal criteria

[Mike Collins]

Hi All,

Not 100% sure how to explain this problem (I even struggled to think of a title for the post).

I have attached a sample spread sheet, with annotation that I think explains the problem more fully.

I have a list of students on courses - students have an ID, course have a code. One student may appear in the sheet multiple time, each entry with a different course. Each student is given an average point score as a decimal value, which is consistent across all subjects that they are taking:

{ID}_{Name}_{Point Score}_{Course Code}_{Course Title}_{Predicated Grade}
1____John_______4.5_________ABC________Science
1____John_______4.5_________DEF________Maths

On another sheet, I have a list of all courses (Course Code & Title) listed vertically. Horizontally I have grades (A, B, C, D, E, U). For each course, I then have the numeric grade that indicates the lower boundary for a given alphabetic grade - these are different for each course:

{Course Code}_{Course Title}_{A}___{B}___{C}___{D}___{E}___{U}
ABC___________Science_____7.2____6.2___ 5.2___4.2_____4____3.8
DEF___________Maths_______6.8____5.8___4.8___4.0_____3.8___3

What I need to do, in the student sheet, is look up the predicted Alpha grade, based on the students Course Code and Point Score.

So, in the example above, Student 1 (John) on course ABC (Science), with a Point Score of 4.5, would have a Predicted Grade of 'D'.

I have tried a variety of different LOOKUP's and INDEX / MATCH combinations but the problem is that when I am searching for the Course Code (from Student in Course) I need an exact match but when I am looking for the Point Score (from Student in Course) I need an approximate match. In addition, I cant see how to return the Alpha grade.

Any help or advice would be gratefully received

Mike

 

[Luke M]

Hi Mike,

This gets a little tricky to read, but the basics is that we'll use MATCH and INDEX functions to figure out which column/row to look at. Note, I had to reverse the order of the grades in Course Table, because MATCH works better when searching left-to-right for "value equal to or less than" goal value. Hopefully that change is acceptable? (in your workbook, if you need to do this, can select data, and then do a horizontal sort based on row 2).

 

[bosco_yip]

Use the OP's original file and using the closest match Lookup formula.

In G3, formula copy down :

=IFERROR(LOOKUP(1,0/FREQUENCY(-D3,-INDEX(N$3:S$7,MATCH(E3,L$3:L$6,0),)),N$2:S$2),"")

Regards
Bosco

 

[eibi]

Using the original file, paste in G4 and copy down to fill:

=INDEX(N$2:S$2,,7-(SUMPRODUCT((E4=L$3:L$7)*(D4>=N$3:S$7)*(N$3:S$7<>""))))

 

[Mike Collins]

Hi Luke. Thanks for the reply and proof. No major problems with reversing the order. Out of the three replies here - yours is the only one I actually understand - innermost MATCH to find the course row, next MATCH to find the grade boundary, outermost INDEX to return the grade.

 

[Mike Collins]

Hay Bosco & Eibi, thanks for the reply and proof - both works perfectly. Could you elaborate on the mechanics of how they work? Your formula(s) work but I don't understand.

 

[Luke M]

Glad you liked it, Mike. Thanks for the follow-up.

 

[eibi]

Mike -- a brief explanation of my suggestion:

=INDEX(N$2:S$2,,7-(SUMPRODUCT((E4=L$3:L$7)*(D4>=N$3:S$7)*(N$3:S$7<>""))))

Beginning from the end:

Test 1:

N3:S7<>"" tests of every cell of the lookup array, returning TRUE if the cell is not blank, and FALSE if the cell is blank. The result is an array like this: {True, True, True, True...; True, True, True...; False, False, False}​

Test 2:

D4>=N3:S7 tests every cell of the lookup array, returning TRUE if the value is smaller than D4, and FALSE if it is not. The result array looks similar to the array described above.​

Test 3:

E4=L3:L7 tests the cells in Column L, returning TRUE for the row that matches E4, returning a smaller array, something like this: {False; True; False; False...}​

Next, these 3 arrays are multiplied together, forcing the TRUE/FALSE values to binary values (1) and (0). The result is that Test 1 zeros out the blank rows, Test 2 zeros out the scores that are higher than D4, and Test 3 zeros out every row except the row that matches E4.

The final array has all zeros EXCEPT where all three tests are TRUE.

SUMPRODUCT counts the remaining TRUE (1) values, and this happens to provide a column number (from 1 thru 6)...but it's backward (as @Luke M pointed out in his solution), so we have to subtract from 7 in order to reverse the 'backward' problem.

Finally, this column number is fed to the INDEX formula, and it looks up the corresponding letter grade in N2:S2.

Hope this helps.

 

[bines53]

Hello friends,

It is also possible that,

=INDEX($K$2 : $P$2,,MATCH(TRUE,INDEX(D3>=INDEX(K$3 : P$7,MATCH(E3,I$3 : I$6,0),),0),0))

David