Got a formula for comparing two sheets, need to adjust for an extra culumn.

[VahidSamimi]

Hey guys,

"So I have two items lists with prices, one with prices from 2017 and the other with the prices of 2018. I need to combine the two items list and find out the difference in price between the years. the problem is that there are some items in the 2017 list that are not in the 2018 list and vice versa, so the lists do not exactly match in the rows. There is however a "material number" for each item to use as a reference if needed."

I posted this earlier and somebody made a formula for me that worked awesome. I applied the formula to 4/5 of the set of lists I need to complete. The problem is on the last set of lists, there is an extra column with an extra cost and I am not sure how to edit the formula to account for the extra column.

This is the formula I used:

Named ranges:

List1

Refers to:='2017'!$A$2:$A$13

List2

refers to:

='2018'!$A$2:$A$13

in Difference:

in A2

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($A$1:A1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($A$1:A1, List2), 0))), "")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy down

In B2

=IFERROR(VLOOKUP($A2,'2017'!$A$2:$H$13,COLUMNS($A:B),0),VLOOKUP($A2,'2018'!$A$2:$H$13,COLUMNS($A:B),0))

Copy Acroos to F and down

in G2

=IFERROR(VLOOKUP($A2,'2017'!$A$2:$H$13,7,0),"")

in H2


Repeat in I & J changing 2017 to 2018


=IFERROR(VLOOKUP($A2,'2017'!$A$2:$H$13,8,0),"")


Ill attach the file I need it to be done to so you can see exactly what needs to be done. The third sheet will be the sheet where It lists the prices of each year, and then the difference between the prices. I can calculate the difference in the prices myself so you don't have to waste your time with that, I mostly need help just joining the lists together and having the prices side by side.

Thanks in advance!

[avk]

You are in right path. I have just few changes.
Also you can use index match instead of vlookup.
Look attach file.