Automate Excel: Update list with new values

=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))) + ENTER.

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)

Extract shared values between two columns

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Filter common values from three separate columns

Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Filter shared records from two tables

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

Compare two lists and filter unique values where the sum in one column doesn’t match the other column

Sean asks: How would you figure out an unique list where the sum of in one column doesn't match the […]

Filter values that exists in all three columns

This article explains how to extract values that exist in three different columns, they must occur in each of the […]

Compare two columns and return differences

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

Compare two columns and extract differences

This article demonstrates a formula that extracts values that exist only in one column out of two columns. There are […]

Comments (5)

5 Responses to “Automate Excel: Update list with new values”

Kanti Chiba says:

December 20, 2010 at 5:03 am

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

Reply
Oscar says:

December 20, 2010 at 8:43 am

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!

Reply
jeevinder singh says:

June 23, 2015 at 1:50 pm

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)

Reply
Sindhunata says:

November 6, 2015 at 4:43 am

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?

Reply
CHRIS says:

May 2, 2018 at 4:48 pm

1. Create one column and do a VLOOKUP for your price in your 'new price sheet;
2. create a second column with the code: 'IFNA'

if your price sheet has #NA select the cell with new price, if it has a value from the old sheet basically its NOT #n/a ; and Excel will keep the old price.

suppose you have an old price in the Cell?
you can create a third column, use your new price sheet and turn all prices in your master sheet to the text '#N/A' then do steps 1 & 2.

Granted this is a 3 column, 3 step process,
But the time and grief saved creating a single formula solution that actually works in the end is priceless.
and you can create a Macro to do this.

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form