Exceljet

Quick, clean, and to the point

VLOOKUP override output

Excel formula: VLOOKUP override output
Generic formula 
=IF(VLOOKUP()=x,y,VLOOKUP())
Explanation 

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

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

where key is the named range B5:C9.

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

How this formula works

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.