Forum Discussion
Excel searching, matching and editing information from one table to another
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.
Is it possible for you to upload or post a copy of your spreadsheet? If not the actual--if it contains private or proprietary information--perhaps a mockup that resembles it.
If not, then you'll need to answer a few questions which seeing the actual would circumvent.
Are the "numbers" in each row--the "123" in your example--actual unique identifiers for the rest of the data? If so, you should be able to use VLOOKUP or one of the similar functions to do the comparison. Then an IF function could be used to change the prices in the first sheet to reflect those in the second.
It would help us help you, though, if you could post a copy of your actual sheet.
mathetes
Thank you for your answer! I've attached both of tables and in the second I've marked the changes with yellow. Can you check it, please. Thank you!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.
Lol, I never imagined that such a thing could be done in excel like this way. Thank you, fantastic job! I like how it works, but after 1 month for example, when I have to enter a new prices after since I have already had a change in the column "Adjstd Price", it will compare the primary prices in column "Price" again and in reality this column becomes redundant. I hope you understand what I mean or you made this solution only for comparing the prices from both tables and I should ignore the "Price" column for future, or?
