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:

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.

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.