VLOOKUP override output
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.
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.
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
