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. 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
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.
