Great, what a massive help. I had to make a few tweaks (my worksheets were
named something different and there were other columns in between the actual
two that mattered) but they were easy to figure out.
Thank you again.
--
Louis


"David Benson" wrote:

Louis,

Let's assume that in worksheet WS1, you have labels in Row 1, and your data
starts in Row 2. In Cell C2 of worksheet WS1, enter the following formula:

=if(iserror(match(a2,ws2!$a$1:$a$5000,0)),b2,index (ws2!$b$1:$b$5000,match(a2,ws2!$a$1:$a$5000,0),1))

Copy this formula down as many rows as you have data for in worksheet WS1.
Make sure that the workbook has been completely calculated, then copy the
prices from Column C of WS1 and do a Paste Special Values into Column B.

Here's what the formula does:

match(a2,ws2!$a$1:$a$5000,0) - Looks in Column A of WS2 to see the the
Product # in Cell A2 appears. If it does appear, the function returns the
row # in which it appears. If it doesn't appear, the function returns the
#REF! error.

iserror(...) - If the "match" function above returns the #REF! error, it
would normally result in the formula as a whole returning the same error.
The "iserror" function prevents that - it returns "TRUE" if the "match"
function returns an error, thereby allowing you to test for the error
condition without being overcome by it.

index(ws2!$b$1:$b$5000,match(...),1) - Now that we know that the Product #
in Cell A2 appears in worksheet WS2, we use the "match" function a second
time to tell the "index" function where to the find the corresponding new
price from column B.

Good luck!

-- David B

"Louis" WS2 for each
item into WS1. Not all items have new pricing, so if there isoft.com
wrote in message
...
I have a master worksheet (WS1), and a new worksheet (WS2) with updated
pricing on about 3K items. I need to put the new pricing no change, or
the item isn't even listed in WS2, then I need the old price to be left as
it
is. I am only concerned with 2 columns of data, Column A being product #,
and Column B being Price.
Simply put, I want excel to look at product A on WS2, and if that product
is
on WS1, then I want to update WS1 with the new price.
It would also be very nice if when product A on WS2 was not recognized it
was highlighted or put into a new column or something, since I will need
to
add those new items eventually. But most important I need to update
pricing
on these items.

--
Louis