Convert percentages to letter grades with a VLookup formula in Excel.
We are going to make some student report cards in excel and we have student percentages and we want to turn those into letter grades. We have a look up table that tells us where each grade level starts and instead of looking that up manually, we’re going to let excel do the work for us with the VLOOKUP formula. The report has two columns. The first column has the percentage and we’re going to fill in the grades here. On another sheet in the work book, we have a table with a less of percentages and the grades that would match those. We start that table with the lowest possible score, the lowest possible percentage which is zero. So if you get a zero, you’re going to get an F. If you get up to a 50 then you would get a D so anything between zero up to 49, you’d get an F starting at 50, you’d get a D and so on. 60 is a C, 70-B, 85 is an A. So in our report card, we’re going to use VLOOKUP formula and we start with an equal sign and then type VLOOKUP and an open bracket. It asks them for the look up value and that’s the percentage that we want to look up so I’m going to click on the 77 and type a comma to start the next argument which is the table array. So, where are the values that we want to look up? I’m going to go to the grade sheet and I’m going to select the cells that have the percentages and letters and on the keyboard, I’ll press the F4 key. So instead of B4 to C8, you’ll see dollar signs at it and that locks in that range. So, if we copy the formula later, it’s always been a look in that exact range for the scores. Then we type another comma and it wants to know the column index. So, in our look up table, we have two columns. Our values are in the first column so we want the grade returned from column number two. So, I’ll type a two and then another comma and we’re not looking for an exact match. Our grade of 77 isn’t in here but 70 is approximate so if we’re a 77 we like it to stop at 70 and show us that that grade gets a B. So, we’ll type through here and around bracket to close and press enter. So, we can see that 77 is a B. Now, if I saw at that cell and point to the little handle in the corner, the auto-fill handle, I can drag that down to see the rest of the grades.