How to Use Conditional Formatting Based on VLOOKUP in Excel (3 Criteria)

Get FREE Advanced Excel Exercises with Solutions!

In this article, you learn how to apply Conditional Formatting based on the VLOOKUP function with 3 different criteria in Excel.


How to Use Conditional Formatting Based on VLOOKUP in Excel: 3 Criteria

This section will help you to learn how to do Conditional Formatting in Excel to format worksheets using the VLOOKUP function.

1. Conditional Formatting to Compare Results Based on VLOOKUP in Excel

In this phase, we will learn how to compare results between two sheets based on VLOOKUP with Conditional Formatting in Excel.

As shown in the picture below, we have a dataset of students’ Names and Semester results in the Semester sheet.

In another sheet named Retake, we have a dataset of students’ Names and Retake results.

Now we will compare these two sheets and find out which student scored less in the semester exam that they had to take the retake exam with the help of Conditional Formatting and the VLOOKUP function.

Steps:

  • Select the cells that you want to format (e.g. all the cells except headers from the Semester sheet).
  • Then in the Home tab, select Conditional Formatting -> New Rule

Conditional Formatting Based on VLOOKUP

  • In the Edit Formatting Rule pop-up window, select Use a formula to determine which cells to format as Rule Type, and in the Edit the Rule Description box write the following formula,
=VLOOKUP($B5,Retake!$B$5:$C$12,2,FALSE)>$C5

Here,

$B5 = cell reference number of the first cell in the Semester sheet
Retake! = 2nd sheet to compare
$B$5:$C$12 = cell range to look up the value
2 = corresponding column number to extract the value from
FALSE = to get the exact match
$C5 = to compare the value with

  • Next click Format.

Conditional Formatting Based on VLOOKUP with formula

  • Go to the Fill tab in the Format Cell window and pick any colour that you like.
  • Click OK.

  • Again click OK on the Edit Formatting Rule window.

Conditional Formatting Based on VLOOKUP for comparing results

The result is shown in the picture below.

Conditional Formatting Based on VLOOKUP output for matching result

In our dataset, only “Ponting” and “Brett” had scored relatively low so the result is highlighting their names and results.

Read More: How to Format Cell Based on Formula in Excel


2. Conditional Formatting to Match Results Based on VLOOKUP in Excel

In this part, we will see how to match results among two sheets in Excel using Conditional Formatting based on VLOOKUP.

Look at the following picture where we have data on some student toppers from different departments in the Topper sheet.

And in another sheet named List, we have a list of students’ names from one department.

So now we will see how to highlight only the data of the student toppers from the only department list that we have.

Steps:

  • As shown in the previous phase, select the cells that you want to format (e.g. all the cells except headers from the Topper sheet) and in the Home tab, select Conditional Formatting -> New Rule.
  • In the Edit Formatting Rule pop-up window, select Use a formula to determine which cells to format as Rule Type, and in the Edit the Rule Description box write the following formula,
=NOT(ISNA(VLOOKUP($B5,List!$B$5:$C$12,1,FALSE)))

Here,

$B5 = cell reference number of the first cell in the Topper sheet
List! = 2nd sheet to compare
$B$5:$C$12 = cell range to look up the value
1 = corresponding column number to extract the value from
FALSE = to get the exact match
The ISNA function is to check whether the value is #N/A or not. If it is then it will return TRUE, otherwise FALSE.

Conditional Formatting Based on VLOOKUP formula for multiple condition

  • Next, similar to before, click Format, pick a color from the Fill tab, and click OK back to back twice.

The result is shown below.

Conditional Formatting Based on VLOOKUP output for matching result

Only the names “Hussey” and “Gilchrist” were in the List sheet in our workbook so those two names are highlighted in the Topper sheet.


3. Conditional Formatting for Multiple Conditions for the Same Range Based on VLOOKUP in Excel

We can also utilize Conditional Formatting for multiple conditions with the VLOOKUP function in Excel.

Consider the following data. We will format the Order Qty. into three categories based on the Quantity predefined by the seller.

Steps to do that are,

  • As shown in the previous phase, select the cells that you want to format (e.g. all the cells except header in the Order Qty. column) and in the Home tab, select Conditional Formatting -> New Rule
  • In the Edit Formatting Rule pop-up window, select Use a formula to determine which cells to format as Rule Type and in the Edit the Rule Description box write the following formula,
=ABS(E5-VLOOKUP(B5,$G$5:$H$12,2,FALSE))<=10

Here,

E5 = cell reference number of the first cell in the Order Qty. column
$G$5:$H$12 = cell range to match the value
2 = corresponding column number to extract the value from
FALSE = to get the exact match
The ABS function is for returning the absolute value of a number without its mathematical sign (e.g. +/- signs).

Conditional Formatting Based on VLOOKUP formula for multiple condition

  • Next, similar to before, click Format, pick a color from the Fill tab (we picked Green), click OK and OK.

The result is shown below.

Conditional Formatting Based on VLOOKUP output for one condition

 

  • Repeat the steps from selecting the cells to writing the formula. This time write the formula as,
=AND(ABS(E5-VLOOKUP(B5,$G$5:$H$12,2,FALSE))>10,ABS(E5-VLOOKUP(B5,$G$5:$H$12,2,FALSE))<30)

Here,

E5 = cell reference number of the first cell in the Order Qty. column
B5 = to match the Product ID
$G$5:$H$12 = cell range to match the value
2 = corresponding column number to extract the value from
FALSE = to get the exact match

  • Click Format, pick a color from the Fill tab (we picked Red this time), click OK and OK.

The result is shown below.

Conditional Formatting Based on VLOOKUP output for two condition

 

  • Again repeat the steps from selecting the cells to writing the formula. And now write the formula as,
=ABS(E5-VLOOKUP(B5,$G$5:$H$12,2,FALSE))>=30

Here,

E5 = cell reference number of the first cell in the Order Qty. column
B5 = to match the Product ID
$G$5:$H$12 = cell range to match the value
2 = corresponding column number to extract the value from
FALSE = to get the exact match

  • Click Format, pick a color from the Fill tab (we picked Blue this time), click OK and OK.

The result is shown below.

Conditional Formatting Based on VLOOKUP output for three condition


Download Practice Template

You can download the free practice Excel template from here.


Conclusion

This article showed you how to apply the Conditional Formatting command with the VLOOKUP function in Excel. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


You May Also Like to Explore

<< Go Back to Conditional Formatting Formula | Conditional Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo