## Automate excel: Update list with new values

### Overview

Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price list with new values.

**Sheet 1** is the **old **price list. It contains 5000 products and amounts.

**Sheet2** is the **new **price list. It contains 2000 random products from the old price list with new prices. (There are no new products)

### How to create a new list with the latest prices.

Copy all products from sheet1 into sheet 3

Now let us find out if a new price exists.

### Sheet3, formula in B2:

Double click lower right corner of cell B2.

The formula is copied down to last adjacent product.

### Sheet3, formula in C2:

Double click lower right corner of cell C2 to copy the formula as far down as needed.

I created column B to make sure the values are the same as in sheet2 and to make it easier to understand the formula in cell C2.

### Explaining the formula in cell C2

**Step 1 - Find out if a new price exists**

=IF(ISERROR(**MATCH(A2, Sheet2!$A$2:$A$2001, 0)**), INDEX(Sheet1!$B$2:$B$5000, MATCH(A2, Sheet1!$A$2:$A$5000, 0)), INDEX(Sheet2!$B$2:$B$2001, MATCH(A2, Sheet2!$A$2:$A$2001, 0)))

Match returns the relative position of an item in (Sheet2!$A$2:$A$2001) that matches a specified value (A2).

MATCH(A2, Sheet2!$A$2:$A$2001, 0) returns #N/A. This means "Product AT" can´t be found in sheet2.

ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)) returns **TRUE**.

*Step 2 - Identify sheet and price*

The formula returned **TRUE **in cell C2. This means there is no new price. Let´s find the old price in sheet1 instead.

=IF(ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)), **Formula if TRUE**, Formula if FALSE)

=IF(ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)), **INDEX(Sheet1!$B$2:$B$5000, MATCH(A2, Sheet1!$A$2:$A$5000, 0))**, INDEX(Sheet2!$B$2:$B$2001, MATCH(A2, Sheet2!$A$2:$A$2001, 0)))

INDEX(Sheet1!$B$2:$B$5000, MATCH(A2, Sheet1!$A$2:$A$5000, 0))

MATCH(A2, Sheet1!$A$2:$A$5000, 0) returns 1. "Product AT" is found on the first row on sheet1.

INDEX(Sheet1!$B$2:$B$5000, 1) returns **$43,90**.

*If formula in step 1 had returned False*

If the formula had returned False in cell C2, we would need to look for the new price in sheet2.

=IF(ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)), Formula if TRUE, **Formula if FALSE**)

=IF(ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)), INDEX(Sheet1!$B$2:$B$5000, MATCH(A2, Sheet1!$A$2:$A$5000, 0)), **INDEX(Sheet2!$B$2:$B$2001, MATCH(A2, Sheet2!$A$2:$A$2001, 0))**)

INDEX(Sheet2!$B$2:$B$2001, MATCH(A2, Sheet2!$A$2:$A$2001, 0))

**Download excel example file**automate_excel_pricelist.zip (300 KB)

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**ROW(**reference**)** returns the rownumber of a reference

**IF(**logical_test, [value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**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

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**ROW(**reference**)** returns the rownumber of a reference

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**MATCH(**lookup_value,lookup_array, [match_type]**)**

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

**ISERROR(**value)

Checks whether a value is an error and returns TRUE or FALSE

### Category: Combinemerge

Question: How do I merge two ranges into one list? Answer: Excel 2007 array formula in C2: =IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, […]

Comments(50) Filed in category: Combine/Merge, Excel

Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & […]

Comments(29) Filed in category: Combine/Merge, Excel

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]

Comments(28) Filed in category: Combine/Merge, Excel

Question: How do I merge three columns into one list? Answer: Excel 2007 array formula in D2: =IFERROR(INDEX(List1, ROWS(D1:$D$1)), IFERROR(INDEX(List2, […]

Comments(26) Filed in category: Combine/Merge, Excel

Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]

Comments(11) Filed in category: Combine/Merge, Excel, VBA

I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells . It […]

Comments(7) Filed in category: Combine/Merge, Excel

Mike asks: Oscar, I'm hoping you can help. I am trying to group a number of rows together by the […]

Comments(5) Filed in category: Combine/Merge, Excel

This is an answer to Shawna´s question. The following user defined function merges up to 255 cell ranges and removes […]

Comments(4) Filed in category: Combine/Merge, Excel, User defined functions (udf), VBA

This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]

Comments(3) Filed in category: Combine/Merge, Excel, Finance

### 4 Responses to “Automate excel: Update list with new values”

### 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

Hi,

Using the IFERROR in Excel 2007, you could use the formula below, whereby you do not need the Helper column B.

=IFERROR(VLOOKUP(A2, Sheet2!$A$2:$B$2001, 2, 0), VLOOKUP(Sheet3!A2, Sheet1!$A$2:$B$5000, 2, 0))

Why is it necessary to make the Formula an Array formula, it seems to work just fine witout it

Kanti Chiba,

you are right. It is not necessary to create an array formula.

Column B is not a helper column, it is created to make the formula in column C easier to understand.

Thanks for your contribution!

if you have a price list for products and you want to update that from a new price list the below formula may help

=IFERROR(INDEX($H$2:$I$4, MATCH(A2, $H$2:$H$4,0), 2),B2)

Hi, what if there is some new products in the new price list. And what I want to do is to identify the "values" of these product, so I know the codes of the new products. Can we do that?