# Conditional Formatting Based on VLOOKUP Result – Excel & Google Sheets

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

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.

`=VLOOKUP($F3,$B$3:$C$7,2,FALSE)<72`

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

**Apply Conditional Formatting**

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

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

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

- In the Formula Bar, input our formula.

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

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

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.

`=VLOOKUP($F3,$B$3:$C$7,2,FALSE)`

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.

`=H3<72`

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

Combining all the formulas together yields our original VLOOKUP formula:

`=VLOOKUP($F3,$B$3:$C$7,2,FALSE)<72`

**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:

`=ISBLANK(VLOOKUP($F3,$B$3:$C$7,2,FALSE))`

** **

This is how the formula will evaluate:

**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:

`=AND(VLOOKUP($F3,$B$3:$C$7,2,FALSE)>=72,VLOOKUP($F3,$B$3:$C$7,2,FALSE)<=74)`

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

`=H3>=72`

**Upper Boundary**

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

`=H3<=74`

**AND Function**

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

`=AND(I3,J3)`

Combining the above formulas results in our original formula:

`=AND(VLOOKUP($F3,$B$3:$C$7,2,FALSE)>=72,VLOOKUP($F3,$B$3:$C$7,2,FALSE)<=74)`

**Conditional Formatting – Multiple VLOOKUP Conditions**

You can easily add multiple conditional formatting rules:

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.

`=ISBLANK(VLOOKUP($C3,INDIRECT("Scores!B3:C7"),2,FALSE))`

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

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

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

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