To override output from VLOOKUP, you can nest VLOOKUP in the IF function. In the example shown, the formula in G5 is:


where key is the named range B5:C9.

This formula returns standard output when the score >= 60, and "x" for scores less than 60.

Generic formula



Note: a simpler approach would be to alter the table used by VLOOKUP directly. But this example explains the mechanics of testing and overriding output from VLOOKUP.

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