I often have the need to match something like a student’s mark to a grade which lies within a boundary. For example a grade ‘A’ might cover marks in the range 100-90. I can never remember the correct way to do this, so here it is.
In the Excel spreadsheet, rename one of the worksheets to something appropriate, and type out the grades. They need to go in ascending order. The title of the columns doesn’t matter.
Enter the data required into a different worksheet and then set up the VLookup.
The VLookup needs its parameters subtly setting
- Specify the range to do the vlookup on, but miss off the title row
- Wrap the range in absolute cell reference markers (press F4 on them)
- Either miss off the “Range lookup” parameter so it defaults to “True”, or set it “True”
I found this on this website which explains other uses for VLookup.
