Article updated on December 21, 2017

Today I am going to show you how to quickly compare two tables using conditional formatting. We are comparing two price lists from the year 2010 and year 2011.

To make things more interesting, price list 2011 is not sorted. new products are also introduced. It is quite common that pricelists are huge and a total mess. Excel can help us find differences.

A requirement for these conditional formatting formulas to work, is that column and row headers have identical spelling. The same capitalization is not required.

The questions are:

  • How do I find new products or models compared to previous year?
  • How to identify lowered prices compared to previous year?
  • How to identify higher prices compared to previous year?

New products or models

Conditional formatting formula:

=ISERROR(INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), MATCH(C$10, $C$3:$G$3, 0))<>C11)

Cells are formatted yellow.

Recommended post

Compare price lists

This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting […]

Find lower prices

Conditional formatting formula:

=INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), MATCH(C$10, $C$3:$G$3, 0))>C11

Cells are formatted red.

Recommended post

Compare two lists of data: Filter records occurring in only one list

In this example we are going to use two lists with identical columns in excel 2007. It is easy to […]

Find higher prices

Conditional formatting formula:

=INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), MATCH(C$10, $C$3:$G$3, 0))<C11

Cells are formatted green.

Recommended post

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

How to apply conditional formatting formula

Make sure you adjust cell references to your excel sheet.

  1. Select cells C11:G15
  2. Click "Home" tab
  3. Click "Conditional Formatting" button
  4. Click "New Rule.."
  5. Click "Use a formula to determine which cells to format"
  6. Copy and paste conditional formatting formula in "Format values where this formula is TRUE" window.
  7. Click "Format.." button
  8. Click "Fill" tab
  9. Select a color for highlighted cells.
  10. Click "Ok"
  11. Click "Ok"
  12. Click "Ok"

Recommended post

Compare two lists of data: Highlight common records

Overview In this blog post you will learn how to: Create a countifs function Create a conditional formatting formula and […]

Explaining find lower prices conditional formatting formula in cell C11

=INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), MATCH(C$10, $C$3:$G$3, 0))>C11

Step 1 - Find relative position of current row header in previous pricelist

=INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), MATCH(C$10, $C$3:$G$3, 0))>C11

MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value

MATCH($B11, $B$4:$B$7, 0)

becomes

MATCH("Product A", {"Product A";"Product B";"Product C";"Product D"}, 0)

and returns 1.

Recommended post

How to use the MATCH function

Identify the position of a value in an array.

Step 2 - Find relative position of current column header in previous pricelist

=INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), MATCH(C$10, $C$3:$G$3, 0))>C11

MATCH(C$10, $C$3:$G$3, 0)

becomes

MATCH("Model 1", {"Model 1", "Model 2", "Model 3", "Model 4", "Model 5"}, 0)

returns 1.

Step 3 - Return a value of the cell at the intersection of a particular row and column

=INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), MATCH(C$10, $C$3:$G$3, 0))>C11

becomes

=INDEX($C$4:$G$7, 1, 1)

becomes

=INDEX({27,3, 612,9, 765,6, 872,1, 417,3;266,2, 989,3, 576,7, 768,5, 948,8;213,6, 276, 140,3, 609,5, 6,5;642,8, 159,2, 848,9, 452,2, 574,1}, 1, 1)

returns 27,3.

Recommended post

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

Step 4 - Compare returned value to current value

=INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), MATCH(C$10, $C$3:$G$3, 0))>C11

becomes

27,3>C11

becomes

27,3>27,3

returns FALSE. Cell C11 is not highlighted red.

Download excel example file

compare price lists.xlsx
(Excel 2007 Workbook *.xlsx)

Recommended blog post

How to compare two data sets

This blog post demonstrates how to quickly compare two tables in Excel. Table1 Table2 Create a fourth column and use […]