**Conditional formatting** is a great way to visualize data on a spreadsheet. It can quickly highlight important information. Conditional formatting comes with many presets that you can apply to highlight your data. However, you can also add your own logic to conditional formatting. **VLOOKUP**is a lookup and reference function to find matches in a table or range by **“row”**. In this tutorial, we will see how to apply conditional formatting to cells based on the VLOOKUP formula. We will also look at how to copy conditional formats to other cells.

You will work with the Techcom Spares shop inventory and apply conditional formatting based on VLOOKUP. A small part of the data has been represented in cells A1:I10.

### Apply conditional format based on VLOOKUP

To highlight the products, for which the quantity left in stock is currently less than the quantity required:

- Select cells C3:C10 by dragging from C3 to C10.
- Click
**Home > Conditional Formatting > Add New Rule.** - In the
**New Formatting Rule**dialog box, click**Use a formula to determine which cells to format.** - Under
**Format values where this formula is true**, type the formula:`“=VLOOKUP(B3,$H$3:$I$10,2,FALSE) < D3”`

- Click
**Format**. - In the
**Color**box, select**Red**. - Click
**OK**until the dialog boxes are closed.This will apply the formatting to column A.

### Multiple Conditions for the Same Range

Excel allows us to add multiple formatting rules to the same range. From the previous example, you will highlight the Max Order Quantity into three categories low, medium and high. These will be represented by colors green, yellow and red. In order to do this,

- Select cells E3:E10
- Click
**Home > Conditional Formatting > Add New Rule.** - In the
**New Formatting Rule**dialog box, click**Use a formula to determine which cells to format.**Under**Format values where this formula is true**, type the formula:`“=ABS(E3-VLOOKUP(B3,$H$3:$I$10,2,FALSE))<=10”`

- Click
**Format**. - In the
**Color**box, select Green. - Click
**OK**until the dialog boxes are closed. - Having the same cells selected, repeat steps 2-3, but Under
**Format values where this formula is true**, type the formula:

`“=AND(ABS(E3-VLOOKUP(B3,$H$3:$I$10,2,FALSE))>10,ABS(E3-VLOOKUP(B3,$H$3:$I$10,2,FALSE))<30)”`

- Click
**Format**. - In the
**Color**box, select Yellow. - Click
**OK**until the dialog boxes are closed. - For the third color, having the same range selected, repeat steps 2-3. Under
**Format values where this formula is true**, type the formula:

`“=ABS(E3-VLOOKUP(B3,$H$3:$I$10,2,FALSE))>=30”`

- Click
**Format**. - In the
**Color**box, select Red. - Click
**OK**until the dialog boxes are closed.

Finally, the Max Order Quantity will look like this:

### Copy Conditional Format to another Cell

The conditional format can be copied to adjacent cells very easily. If you have adjacent columns that need to be formatted based on the same logic you can use the Format Painter.

If you want to highlight all the tasks that had a progress of 50% or more, you need to:

- Select cells C3:C7 by dragging by dragging from C3 to C7.
- Click
**Home > Conditional Formatting > Add New Rule.** - In the
**New Formatting Rule**dialog box, click**Use a formula to determine which cells to format**. Under**Format values where this formula is true**, type the formula:

`“=VLOOKUP(D3,$J$3:$K$17,2,FALSE) >= 0.5”`

- Click
**Format**. - In the
**Color**box, select Green. - Click
**OK**until the dialog boxes are closed. - To copy the conditional format from column D to E and F, click on cell D3.
- Click
**Home > Format Painter.** - To paste the conditional formatting, drag the paintbrush icon to cells E3:F7.
- Press
**Esc**. anytime to cancel the Format Painter.This will copy column D’s format to column E and F.

Conditional Formatting is a fantastic tool in Excel. Shipping with essential presets, you can also add your custom made rules to conditional formats. In this tutorial, we saw such an example with the VLOOKUP function. VLOOKUP is an essential lookup and reference function used to extract values based on a match. Along with VLOOKUP and Conditional Formatting, we can create powerful workarounds like these that help us a lot to visualize our data in a clean and tidy manner.

If you have trouble with using VLOOKUP and conditional formatting and want to save hours of researching, try our Excel Chat live help service. Our experts are available 24/7 and ready to answer any Excel related question on the spot. The first question is free.

## Leave a Comment