Exceljet

Quick, clean, and to the point

Calculate grades with VLOOKUP

Excel formula: Calculate grades with VLOOKUP
Generic formula 
=VLOOKUP(score,key,2,TRUE)
Explanation 

To assign a grade based on a score, you can use the VLOOKUP function configured to perform an approximate match. In the example shown, "key" is the named range B5:C9, the formula in F5. copied down, is:

=VLOOKUP(E5,key,2,TRUE)

where "key" is the named range B5:C9.

Note: the lookup table must be sorted in ascending order, and VLOOKUP must be set to approximate match mode.

How this formula works

In this example, the scores in column F are lookup values for the VLOOKUP function. The lookup table is the named range "key" (B5:C9). The column index is provided as 2, since we want VLOOKUP to return a grade from the second column. Finally, the last argument for VLOOKUP, the confusingly named "range lookup" is set to TRUE, to tell VLOOKUP to perform an "approximate match".

In approximate match mode VLOOKUP assumes the table is sorted by the first column. With a score provided as a lookup value, VLOOKUP will scan the first column of the table. If it finds an exact match, it will return the grade at that row. If VLOOKUP doesn't find an exact match, it will continue scanning until it finds a value greater than the lookup value, then it will "step back", and return the grade in the previous row.

In other words, VLOOKUP will match the last value that is less than or equal to the lookup value. 

Note: if the score is less than the first entry in the table,  VLOOKUP will return the #N/A error.

About approximate match

VLOOKUP will perform an approximate match by default, so there is technically no need to supply the 4th argument. However, I recommend you always provide the last argument explicitly because it forces you to consider what you want. Providing a value also gives you a visual reminder in the future.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.