Sep 18 2019 10:03 AM
There are different classifications for scores based on a student's grade. I can do multiple IF statements (see column D), but that requires filtering by grade and editing the formula several times. Is there a way to do this with a Vlookup?
This does not work (see E2):
=IF(B2=3,(VLOOKUP(c2,$f$2:$g$9,2)),IF(B2=4,VLOOKUP(c2,$f$2:$h$9,2)),IF(B2=5,VLOOKUP(c2,$f$2:$l$9,2)),IF(B2=6,VLOOKUP(c2,$f$2:$j$9,2)),IF(B2=7,VLOOKUP(c2,$f$2:$k$9,2)),IF(B2=8,VLOOKUP(c2,$f$2:$l$9,2)))
Sep 18 2019 10:42 AM
If it's possible to keep your sub level range in reverse order
the formula could be easier, like
=VLOOKUP($C2,INDEX($O$2:$U$2,$B2-1):$V$9,10-$B2)
If I understood your logic correctly.
Sep 18 2019 10:53 AM
Hi,
Please check out the below formula:
=INDEX($F$2:$F$9,
IF(ISNUMBER(MATCH(C2,INDEX($G$2:$L$9,,MATCH(B2,$G$1:$L$1,0)),0)),
MATCH(C2,INDEX($G$2:$L$9,,MATCH(B2,$G$1:$L$1,0)),0),
MATCH(C2,INDEX($G$2:$L$9,,MATCH(B2,$G$1:$L$1,0)),-1)+1))
Hope that helps
Sep 18 2019 11:34 AM
Sep 18 2019 11:36 AM