Go Back

How to Apply Conditional Format Based on VLOOKUP

Read time: 22 minutes

Conditional formatting is a great tool to help 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.

Apply a conditional format based on VLOOKUP

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.

Example of using VLOOKUP for conditional formatting

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

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

  5. Click Format.
  6. In the Color box, select Red.
  7. Click OK until the dialog boxes are closed.

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. To do this,

  1. Select cells E3:E10
  2. Click Home > Conditional Formatting > Add New Rule.
  3. 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”

  4. Click Format.
  5. In the Color box, select Green.
  6. Click OK until the dialog boxes are closed.
  7. 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)

  8. Click Format.
  9. In the Color box, select Yellow.
  10. Click OK until the dialog boxes are closed.
  11. 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

  12. Click Format.
  13. In the Color box, select Red.
  14. 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 progress of 50% or more, you need to:

  1. Select cells C3:C7 by dragging by dragging from C3 to C7.
  2. Click Home > Conditional Formatting > Add New Rule.
  3. 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
  4. Click Format.
  5. In the Color box, select Green.
  6. Click OK until the dialog boxes are closed.

  7. To copy the conditional format from column D to E and F, click on cell D3.
  8. Click Home > Format Painter.

  9. To paste the conditional formatting, drag the paintbrush icon to cells E3:F7.
  10. 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 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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

For the midterm project you will design and create a workbook. The workbook should help solve some problem or serve a purpose that is of personal value to you, a friend or an associate. Create a short description of the workbook you plan to create as descrbied in Part I of the project. Keep in mind that your workbook must implement the technical requirements described in part II. CS302 Midterm Project Part I: Write a description of the Excell workbook you plan to create. Be sure to choose a project concept that will be of personal use to you, a friend or work associate. Preview your description with your instructor for approvail before proceeding to part II In your description include the following: 1. Who will use the workbook 2. Describe what the workbook will do (What purpose will it serve?) 3. Describe the type of data that will be stored in the workbook 4. Describe some of the analysis and reporting features of the workbook. Midterm Project Part II: Create the workbook to complete your project. Your workbook must implement the following technical requirements. 1. Well formatted with some background color, borders and style 2. Contain at least one database sheet with at least 15 records and 4 fields of data and at least one calculated field. 3. Apply appropriate named cell references to your workbook. 4. Implement goal seek analysis using absolute cell references and an assumption area. 5. Use conditional formatting on the database sheet. 6. Use two or more of the functions from this list: if(), iferror(), sumif(), countif() 7. Use a database lookup function like VLOOKUP() using a lookup table. 8. Create a filter sheet that will demonstrate the use of numberic and text filters applied to your datasheet. 9. Create some scenarios and generate a summary report. 10. Add at least one macro with a button to run the macro. 11. Use solver in some way. Be sure to create an answer report. 12. Add appropriate and consistant data validation fields 13. Create 2 charts of different styles using different data. Include examples of data labels, modify axis settings, legends and titles. 14. Add an imbedded image with a link to a web web site. 15. Add cell and worksheet protection. Highlight the unprotected cells. Make the password "MIDTERM". 16. Be sure to add comments to your project workbook pointing to each of the requirements above. Include the requirement number as the first line in the comment. The comment should point to where you implemented each of the technical requirements. To add comments right click the cell and select the insert comments menu option. 17. Add a sheet to your finished workbook named INDEX. The INDEX will list each of the technical requirements above. The INDEX sheet should be the first sheet in your sheet tab. In the A column of the INDEX sheet create a list titled 1 thru 16 matching up with the 16 technical requirements. Add a hyperlink so that when the instructor clicks on the link it will goto the worksheet and cell containing an example of the technical requirement. In the B column add a short description of your implementation of the technical requirement. This is required for grading purposes. If these hyperlinks are not added to your workbook it will affect your project grade. Submit your description document and project workbook for grading.
Solved by C. J. in 23 mins
hello I want to make a time sheet in excel for the company employees its attached I don't want to create a separate file for each employee then multiple sheets in each file for each month I saw some files online where you create a drop down list for employees then whenever you choose an employee you get his own data in the same sheet is this related to conditional formatting and can only be applied when the changes between employees data are only in formatting? is it related to VLOOKUP? I also want to apply the same concept to years/months so I can stay in the same sheet while viewing the details for all employees for whatever year or month I want thanks
Solved by T. S. in 29 mins
Hello, when I use a vlookup it it not picking up some of the information because the formatting isn't the same. I have tried to change the cell formatting to match but it is still not picking up the data.
Solved by S. E. in 28 mins
Hi I have column of registered attendees to an event and then a second column of actual attendees. i need to be able to identify what attendees attended and those that didnt. i have tried applying conditinal formatting but it does not account for any variations in fonts or spacing. i also tried IF and Vlookups but i cant get anything to work.
Solved by B. E. in 15 mins
I am attempting to add conditional fomatting to column C, however, the cells with the VLOOKUP formula in them will not reflect the formation. C3-C6 do not have formulas and show the conditional formatting can you assist? The formatting should process from red to green from 20 to 70 being the all green,
Solved by D. D. in 18 mins
I am formatting a column of number/letters to be stored as text. A formula relies on a vlookup with this column being used as the first criteria. It should work fine however I must go into each cell in the column, hit F2, then hit enter for the formula to identify it is stored as text. Is there an easier way to do this for the entire column since it is thousands of cells?
Solved by T. C. in 14 mins
I have 2 sheets containing data. The first sheet is a "map" with bin locations and the second sheet contains a list of products and which bin they belong to, but the locations are separated into 4 columns.("Aisle", "Rack", "Level" and "Bay." Ideally I'd like to see if there's a way to use conditional formatting to highlight a cell on the 1st sheet if the 4 conditions for a location on the second sheet are met. This is what I've tried so far that hasn't worked: =IFERROR(VLOOKUP(“6”,Inventory!A2:A1691,1,FALSE)+VLOOKUP(“3”1,Inventory!B2:B1691,2,FALSE)+VLOOKUP(“1”,Inventory!C2:C1691,3,FALSE)+VLOOKUP(“61.1”,Inventory!D2:D1691,4,FALSE),"false") =countif((Inventory!$K:$K=6),(Inventory!$L:$L=31), (Inventory!$M:$M=1), (Inventory!$N:$N=61.1)) Help?
Solved by S. L. in 16 mins
I have a vlookup formula but it seems that excel is not recognizing the lookup value in the cell. I already checked that the formula is correct, and the formatting is not text. Any other reasons why it isn't recognizing the cell value?
Solved by S. B. in 12 mins

Leave a Comment

avatar
Jagdish
Guest
Jagdish

Hi

Comment awaiting moderation

Jagdish
Guest
Jagdish

I need help double vlookup in same file as after 1st vlookup I want give if conditions and add another vlookup in same cell in excel

Comment awaiting moderation