vlookup by a set of values in a cell

[Legacy 319243]

Hi,

I've created a spreadsheet for all classes in my school, so we can track progress and compare grades against target grades. The current version works really well.

Now we're getting to the end of the school year, students are sitting different tiers based on what papers they have been entered for in the summer (either Higher 'H' or Foundation 'F').

I have a good vlookup thing going with for raw scores, that looks up grades on another sheet based on these scores, for example:

=VLOOKUP(N3,'Grade Boundaries'!$AC$20:$AD$28,2,TRUE)

This works well, if all the students are sitting the same papers, which now they are not, and it will take a LONG time to manually filter through all the students and enter a new formula based on what tier grade boundary I want it to show.

I have created a column for each student and teachers will enter in what paper the student will be sitting (either H or F)

So... My question is this...

Can I create a vlookup based on what another cell contains? for example if cell M3 contains 'H' then run the vlookup I have shown above, at a specific range of grade boundaries, or if it contains an F then vlookup a different range of values.

Thanks so much for your help in advance, and I hope I've made sense.

Here is a link to the document

https://docs.google.com/spreadsheets/d/1h-Y2rOpN-50V-VBprkKAN5xn5Qn6R1-Qfa6i1nWsXd0/edit#gid=0

Im currently experimenting in the first sheet (core Sc yr10) cell L3 - N3 (L3 is the H or F, M3 is the raw test score, and N3 is the output grade based on the vlookup of cell L3 being either H or F


Thanks!!!

Chris

 

[etaf]

=if( m3="H", VLOOKUP(N3,'Grade Boundaries'!$AC$20:$AD$28,2,TRUE) , different vlookup)

will that work for you ?

the link is asking for permission to open

 

[Legacy 319243]

think link sharing should be on now. What do I place in the 'different vlookup part. Sorry. Still learning excel, on the job.

thanks

chris

 

[etaf]

you said

or if it contains an F then vlookup a different range of values.

so i assumed you knew what range to add to the different vlookup
=VLOOKUP(N3,'Grade Boundaries'!$AC$20:$AD$28,2,TRUE)

so the range to change would be
=VLOOKUP(N3,'Grade Boundaries'!$AC$20:$AD$28,2,TRUE)

but i dont see how that "works well" on the sample

=VLOOKUP(N3,'Grade Boundaries'!$AC$20:$AD$28,2,TRUE)

This works well

AC20 - AD28 are all blank