## Compare pricelists in excel 2007

This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting formulas and highlight:

- new products yellow
- prices higher than previous year green
- prices lower than previous year red

**Sheet1 - Year 2011**

**Sheet2 - Year 2010**

### Create named ranges

You can´t reference cells outside current sheet when constructing a conditional formatting formula unless you use named ranges.

category2010 - B3:B12

product2010 - C3:C12

price2010 - D3:D12

- Select sheet2
- Select B3:B12
- Type category2010 in name box
- Repeat step 1-3 with remaining names and cell ranges

### Highlight new products yellow

Conditional formatting formula:

### Highlight higher prices green

Conditional formatting formula:

### Highlight lower prices red

Conditional formatting formula:

### How to apply conditional formatting formula

- Select sheet1
- Select cell range B3:D14
- Click "Home" tab
- Click "Conditional Formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- Copy and paste conditional formatting formula in "Format values where this formula is TRUE" window.
- Click "Format.." button
- Click "Fill" tab
- Select a color for highlighted cells.
- Click "Ok"
- Click "Ok"
- Click "Ok"

### Download excel file

Compare pricelists_new.xlsx

(Excel 2007 Workbook *.xlsx)

Recommended blog posts

How to quickly compare pricelists in excel

### Functions in this article:

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**COUNTIFS(**criteria_range1,criteria1, criteria_range2, criteria2...**)**

Counts the number of cells specified by a given set of conditions or criteria

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]You can click on headers to sort table for easy finding.

### 7 Responses to “Compare pricelists in excel 2007”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

This is exactly what I was looking for.

Unfortunately it does not work in my specific case and after working a few hours I am kinda stuck...

The only differences are the following:

1. I do not have a product column

2. my category and prices column are not adjacent (B and E)

3. I've multiple prices column (month, E, F, G, etc.)

I am trying to compare monthly prices from year 1 (worksheet 1) with monthly prices from year 2 (worksheet 2).

Any brillant ideas would be very much appreciated :-)

So far this is what my latest try:

=$E2>INDEX(price2010, MATCH(1, COUNTIF($B2, category2010), COUNTIF(E$1, month2010)))

Also tried this one:

=E2>INDEX(price2010, MATCH(1, COUNTIF($B2, category2010), MATCH(1, COUNTIF(E$1, month2010))))

price2010 - E2:P20

category2010 - B2:B20

month2010 - E1:P1

I was also expecting a MATCH(0,...) and not a MATCH(1,...)

Now I've reached my technical abilities :-)

Thank you for your help in advance

Géraud,

1. I do not have a product column2. my category and prices column are not adjacent (B and E)

3. I've multiple prices column (month, E, F, G, etc.)

I need to know what columns to compare?

- Compare prices in columns E and F, F and G, G and H etc. on sheet1.

or

- Compare prices in column E on sheet1 and column E on sheet2 and so on..

Dear Oscar,

Thank you for the quick reply.

Indeed I would like to compare prices in column E on sheet1 and column E on sheet2 and so on.

Ideally I would like to compare the prices in each cell of range E2:P20 on sheet 1 with the prices in each cell of range E2:P20 on sheet 2.

I am pretty sure it will work column by column but I guess the INDEX formula should work on both rows and columns?

Cheers, Géraud

Géraud,

Your scenario is a bit different. I´ll try to explain the formula.

Named ranges:data_2010 - Sheet: 2010 Range: E2:G5

headers_2010 - Sheet: 2010 Range: E1:G1

category_2010 - Sheet: 2010 Range: B2:B5

Highlight higher prices greenHighlight lower prices redExplaining conditional formatting formula applied to cell E2The formula compares the value on sheet 2011 (bolded) with the "correct" value on sheet 2010.

E2>INDEX(data_2010, MATCH(E$1, headers_2010, 0), MATCH($B2, category_2010, 0))To find the "correct" value on sheet 2010 the formula matches the header on sheet 2011 (bolded) with the headers on sheet 2010 (bolded) and returns it´s relative position.

E$1,headers_2010, 0), MATCH($B2, category_2010, 0))MATCH(E$1, headers_2010,0)

becomes

MATCH("Feb", {"Jan", "Feb", "March"}, 0) and returns 2.

It also matches the category (bolded) and returns it´s relative position.

$B2,category_2010, 0))MATCH($B2, category_2010, 0)

becomes

MATCH("C", {"A";"B";"C";"D"}, 0) and returns 3.

=E2>INDEX(data_2010, MATCH(E$1, headers_2010, 0), MATCH($B2, category_2010, 0))

becomes

=303>INDEX(data_2010, 2, 3)

becomes

=303>302 and returns TRUE.

Download excel fileCompare pricelists_geraud.xlsx

Excel 2007 *.xlsx

Recommended blog post:

How to quickly compare pricelists in excel

Hi Oscar,

My last botched attempt was

E2>INDEX(data_2010, MATCH(1, COUNTIF($B2, category_2010), MATCH(1, COUNTIF(E$1, headers_2010))))

but yours is beautiful in its simplicity by removing the countif. I've just swapped between headers_2010 and category_2010 since it's the category that fixes the row # of the INDEX.

=E2>INDEX(data_2010, MATCH($B2, category_2010, 0), MATCH(E$1, headers_2010, 0))

Your formula is brillant and works like a charm now. It's nice when you can automate these kind of customized functions. Takes time at the beginning but saves much time afterwards!

Cheers, Géraud