Comparing two different price lists

[yoavf]

Hi,
Could you please assist?
I'm trying to set a formula that will calculate the price difference between two identical items in two different sheets. The items' order is not the same and each list includes some different items along the identical ones.

[hemesh]

Hello yoavf ! Welcome to the forum !

can you attach a book with sample data
(to attach book go to advanced option and select paper clip icon)

[yoavf]

Thank you,
Here are the two documents.

[hemesh]

can you please update which sheets needed to be compared.

[yoavf]

can you please update which sheets needed to be compared.

In case the field code (column A) in file 1 equals to a code in file 2 (the row number is different), then the prices need to be compared (price field (column C) of file 2 minus price field of file 1).

[hemesh]

what do you want to do there are multiple occurrences of code from file 1 in , file 2

[hemesh]

Hello try this formula in book 1 in D2 (i have changed the sheet name as CMES from you book 2, you also need to change the name of first sheet as CMES)

=IF(SUM(--(IF(A2=[2.xlsm]CMES!$A$15:$A$498,1)))>0,SUM(--(IF(A2=[2.xlsm]CMES!$A$15:$A$498,[2.xlsm]CMES!$C$15:$C$498)))-C2,"")
copy and paste then hold control and shift together and then hit enter to make it array formula and drag down till end of product!

above formula will find the matching code in sheet 2 if there are multiple occurrences then it will sum their values and then will deduct the price field 2 - price field 1
if there are no matches then cell will be blank

[hemesh]

formula in column D in 1 is array formula you need to hold control and shift together and then hit enter to make it array formula if you
change something.
(once formula is entered as array you will find curly braces {} surrounding your formula)

[yoavf]

formula in column D in 1 is array formula you need to hold control and shift together and then hit enter to make it array formula if you
change something.
(once formula is entered as array you will find curly braces {} surrounding your formula)

I'm getting a #VALUE! error in all of the cells.
Thanks for the help.

[hemesh]

did you changed the sheet name and did you entered formula as array formula ?

[yoavf]

did you changed the sheet name and did you entered formula as array formula ?

yes, it says that a value used in the formula is of the wrong data type.

[popipipo]

You can use the VLOOKUP formula

=IFERROR(VLOOKUP(A10,[1.xlsm]מחירון!$A$1:$C$648,3,0),"")