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 press with left mouse button on 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))

Get 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 […]

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

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

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) […]

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 […]

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