VLOOKUP override output

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:

=IF(VLOOKUP(F5,key,2,TRUE)="F","x",VLOOKUP(F5,key,2,TRUE))

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:

=IF(F5<60,"x",VLOOKUP(F5,key,2,TRUE))

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.