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:


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.

Generic formula



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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.