Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to apply conditional formatting based on VLOOKUP in Excel?

This article will guide you to apply conditional formatting to a range based on VLOOKUP results in Excel.


Apply conditional formatting based on VLOOKUP and comparing results

For example, you listed all student scores and the last semester scores in two worksheets as below screenshot shown. Now you want to compare scores in two worksheets, and highlight the rows in the Score sheet if scores are higher than those in last semester. In this situation, you can apply conditional formatting based on VLOOKUP function in Excel.

1. In the Score worksheet, select the student scores except the headers (in my case I select B3:C26), and click Home > Conditional Formatting > New Rule. See screenshot:

2. In the New Formatting Rule dialog, please do as follows:
(1) Click to select Use a formula to determine which cells to format in the Select a Rule Type list box;
(2) In the Format values where this formula is true box, please enter this formula =VLOOKUP($B3,'Score of Last Semester'!$B$2:$C$26,2,FALSE) < Score!$C3;
(3) Click the Format button.

Note: in above formula,

  • $B3 is the first student name cell in the Score sheet;
  • 'Score of Last Semester'!$B$2:$C$26 is the last semester score table in the Score of Last Semester sheet;
  • 2 means look for values in the second column of the last semester score table.
  • Score!$C3 is the first score cell in the Score sheet.

3. In the Format Cells dialog, go to Fill tab, select a fill color, and then click OK > OK to close two dialogs.

Now you will see if the score of a student in the Score sheet is higher than that in the Score of Last Semester sheet, the row of this student will be highlighted automatically. See screenshot:


Apply conditional formatting based on VLOOKUP and matching results

You can also apply the VLOOKUP function to match values in two worksheets, and then apply conditional formatting based on the VLOOKUP and matching results in Excel. Please do as follows:

Note: Drag the y scroll bar to view above picture.

For example, I have a winner list in Sheet1, and the roster of students in Sheet2 as below screenshot shown. Now I will match the winner list and roster of students with VLOOKUP function, and then highlight matched rows in winner list.

1. Select the winner list except headers, and click Home > Conditional Formatting > New Rule.

2. In the New Formatting Rule dialog, please do as follows:
(1) In the Select a Rule Type list box, please click to select Use a formula to determine which cells to format;
(2) In the Format values where this formula is true box, please enter this formula =NOT(ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE)));
(3) Click the Format button.

Note: In above formula,

  • $C3 is the first name in the winner list;
  • Sheet2!$B$2:$C$24 is the roster of students in Sheet2;
  • 1 means to look for matched value in the first column of roster of students.

If you need to highlight unmatched values, you can use this formula =ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE))

3. In the Format Cells dialog, go to Fill tab, click to select a fill color, and click OK > OK to close both dialogs.

Now you will see if a name in the winner list matches (or does not match) with roster of students, the row of this name will be highlighted automatically.

Apply conditional formatting based on VLOOKUP and matching results with an amazing tool


If you have Kutools for Excel installed, you can apply its Select Same & Different Cells feature to easily apply conditional formatting based on VLOOKUP and matching results in Excel. Please do as follows:

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 60-day, no credit card required! Get It Now

1. Click Kutools > Select > Select Same & Different Cells to enable this feature.

2. In the Select Same & Different Cells dialog, please do as follows:
(1) Select the Name column of winner list in the Find Values in box, select the Name column of student roster in the According to box, and it’s optional to check the My data has headers option as you need.
(2) Check the Each Row option in the Based on section;
(3) Check the Same values or Different Values option as you need in the Find section;
(4) Check the Fill backcolor option, and select a fill color from below drop-down list;
(5) Check the Select entire rows option.

3. Click the Ok button to apply this feature.

Now you will see all rows with matched (or unmatched) values are highlighted and selected at once, and simultaneously a dialog box comes out and tells how many rows have been selected.


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.