Excel searching, matching and editing information from one table to another

bardukiss 

Here are two possible solutions for you. The first will work with older versions of Excel, for it uses a formula to check the (potentially) older prices and delivery times against the new that looks like this:

=IF(INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0))<>C2,INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0)),C2)

The heart of that formula is the INDEX/MATCH that retrieves the price from the more recent table ("Table1").

INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0))

The IF function then compares that price to the old price  <>C2

and, if they are unequal,

inserts the new price, INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0)),

but otherwise retains the old price. C2)

The second solution uses a much newer function, LET, that makes it possible to shorten the formula above by only using the INDEX/MATCH formulation a single time, defining a short term value to be the result of that formulation. I'll let you research the LET function on your own--there are various on-line resources that explain it better than I could. If it works for you, I highly recommend learning how to use it, as it can save lots of time and reduce errors in long formulas.

=LET(

AccPrc,INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0)),

IF(AccPrc<>C2,AccPrc,C2)

)

LET allows us to define "AccPrc" as the result of looking up the price, using INDEX and MATCH, in the table that contains the more recent data.

It then uses that "Accurate Price" in the IF formula to compare with the old price and change it to the new if they're not the same.

Then, in both sample workbooks, I used Conditional Formatting to highlight the rows in which the data have been changed.

If you have further questions, please don't hesitate to write back. This is not meant to be the end result....you will probably want to format things differently; I did it like this to make visible the components.