Conditional Formatting Based on VLOOKUP Result – Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Translated by

Dennis Madrid

Last updated on August 6, 2023

This tutorial will demonstrate several examples of how to apply Conditional Formatting based on the result of a VLOOKUP Function in Excel and Google Sheets. If your version of Excel supports XLOOKUP, we recommend using XLOOKUP instead.

conditional formatting based on vlookup


Let’s look at an example where we want to apply conditional formatting based on the result of a VLOOKUP function.


Format based on VLOOKUP Comparison

First, we will apply conditional formatting to the table of Names and Student IDs (col E-H) by looking up each student’s grades (Col B-C) and apply RED cell Fill Color if their scores are below 72. The result is shown here, but we will walk through the steps below.


conditional formatting based on vlookup comparison

Let’s look at how to apply the above formula in conditional formatting.

Apply Conditional Formatting

  1. Highlight the range where the conditional formatting will be applied.

Apply conditional formatting step1

  1. In the Ribbon, go to Home Tab > Styles Group > Conditional Formatting > New Rule.

Apply conditional formatting step2

  1. In the pop-up menu, select Rule Type: Use a formula to determine which cells to format.

Apply conditional formatting step3

  1. In the Formula Bar, input our formula.

Apply conditional formatting step4

  1. Click Format and define your formatting settings. In our case we will choose a RED Fill Color. Click OK.

Apply conditional formatting step5

  1. Now click OK and the Conditional Formatting Rule is applied.

Apply conditional formatting step6


Now that we know how to apply conditional formatting, let’s walk through the formula.

It can be helpful to start by entering your conditional formatting formula(s) into cells to test that they work as you’d expect. We will do so below.


VLOOKUP Function

First, we perform our VLOOKUP.


Conditional formatting vlookup function


We use $s to lock cell references. This is essential to creating a formula that will work for all rows.

Logical Comparison

Next, the results are compared to the set criterion.


Conditional formatting logical comparison

Conditional Formatting will change the cell color for any rows with TRUE.


Combining all the formulas together yields our original VLOOKUP formula:



Format If VLOOKUP is Blank

Notice in the previous example, one student did not have a score. We can detect and highlight these cells by adding the ISBLANK Function:


Format if vlookup is blank

This is how the formula will evaluate:

Format if vlookup is blank desglose paso1


Format If VLOOKUP is within Range of Values

Another common scenario with VLOOKUP is to check if the value is within a given range of values.

To do this, we can use the AND Function together with VLOOKUP:


Format if vlookup is within range of values

Let’s walk through the formula:

Lower Boundary

First, we check if our VLOOKUP’s output is greater than or equal to a given lower boundary (e.g., 72).


Lower boundary

Upper Boundary

Next, we check if that same value is less than or equal to the upper boundary (e.g., 74).


Upper boundary


AND Function

Finally, we use the AND Function to check if both conditions are TRUE.


And function


Combining the above formulas results in our original formula:


Conditional Formatting – Multiple VLOOKUP Conditions

You can easily add multiple conditional formatting rules:

Conditional formatting multiple vlookup conditions


Conditional Formatting rules are applied in order (top to bottom). If Stop if True is checked, then if that condition is met no more formatting rules are tested or applied.


In our particular example, the blank VLOOKUP (e.g., ROW 6) satisfies two conditions, red (e.g., <72) and orange (e.g., ISBLANK). Because the ISBLANK rule is applied first and Stop if True is checked, ROW 6 is highlighted ORANGE because the ISBLANK condition is met and subsequent rules are not tested.


Conditional Formatting based on VLOOKUP Result in Google Sheets

All formulas discussed above work the same way in Google Sheets except if the lookup table is in another sheet. In this instance, we need to use a Named Range or the INDIRECT Function in order to reference ranges from other sheets in conditional formatting.



Here are the steps on how to apply conditional formatting in Google Sheets:

  1. Highlight the range, and then, go to Format Tab > Conditional Formatting

Conditional formatting based on vlookup result gsheets

  1. In the sidebar, go to “Format Rules” and select “Custom formula is.”

Conditional formatting based on vlookup result step2 gsheets


  1. Input our formula in the formula bar, and set the formatting style and hit Done:

Conditional formatting based on vlookup result step3 gsheets

AI Formula Generator

Try for Free

See all How-To Articles