Forum Discussion
Changing vlookup array based on a cell value
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)))
4 Replies
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
- Hatham, Thank you! It worked! It also was more accurate than my original code submitted. I guess I'm going to have to explore the INDEX and MATCH functions.
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.
- Thank you! Than worked perfectly, too, and the 'reversed' order was the original from the source.
