where key is the named range B5:C9. This formula returns standard output when the score >= 60, and “x” for scores less than 60. This formula is based on a simple grading example explained in detail here. For a given score, VLOOKUP uses a existing table, the named range key (B5:C9), to calculate a grade. Note match mode is set to approximate. To override output, VLOOKUP is nested in an IF statement: The literal translation of this formula is: If VLOOKUP returns “F”, return “x”. Otherwise, return the result from VLOOKUP. The result of “x” can be customized as desired. To display nothing, provide an empty string ("").
Alternative formula
A simpler, less redundant formula can be created by using IF to check the score directly like this: However, this formula does not technically override the output of VLOOKUP. Instead, it tests the incoming score value and bypasses VLOOKUP entirely if below 60.
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.
