Lookup Based on Multiple Criteria

[majime01]

Hello All,

I am trying to figure out how to look up a value based on several criteria. I have a list of student reading test results. I need to cross reference the Month the test was taken, the Grade Level of the student, the Chart (Independent or Instructional), the Reading Level, and then return if they were "Above the standard", "Meeting the Standard", "Partially Met the Standard", or "Below Standard".

To add to the challenge:
for All parts of the Chart that are Above the Standard like Row H3:Q3, it is that letter or above (ex: if it has the letter B, then it means B,C,D,E etc.
For All parts of the Chart that are Below the Standard like RowH14:Q14 it is that letter or below (ex: if it has the letter H, the it means H,G,F,E etc.

for example on the attached file: the month is Nov the first student is Grade 1, Reading Level A, Independent. This should return a result of "Below the Standard"

This is indeed a challenge and I appreciate any help you can offer.
The Chart is color-coded to help with the confusion.

Ken

[GeoffW283]

Please try in E2 copied down:

Please Login or Register  to view this content.

The #N/As in the results column are rows where I have not added a date. If that is an issue then the formula can be wrapped with iferror().

Also, just a note - if I understand the logic then I question the R value in cells J8 and K8, they seem to be out of place.

Finally, hopefully the layout is very close to your real file, the formula may not tolerate significant differences. Let us know if you have issues.

Test it carefully and let us know if this meets your need.

[Bo_Ry]

Wild guess E2

=INDEX($G$3:$G$26,MATCH(B2&C2,LOOKUP(ROW($F$3:$F$26),ROW($F$3:$F$26)/($F$3:$F$26>0),$F$3:$F$26)&INDEX($G$3:$AD$26,,MATCH(D2&"*",$G$1:$AD$1,)+MATCH($A$2,$H$2:$Q$2,)),-1))