looking for best formulation for my issue

Hi,

I have two worksheets( W1 & W2). I need to update value of Column "C" in W1 and show updated value in Column "D" of W1.
To update value of column "C" I need to compare value of column "A" and column "B" for each row in W1 with available rows for columns "A" & "B" in W2 worksheet and if they are exactly equal then check for the correspond value of column "C" of W2 and if the value of column "C" of W2 is equal to value of column "C" of W2 then insert same value in Column "D" of W1 else select the value of column "C" in W2 as new value and insert in column "D" of W1. Finally if the value of "A" & "B" columns in both worksheets are not equal then just simply return "DIFFERENT" statement in column "D" of W1.

I like to know what are the best formulation ways to do it.

Thank you in advance.

There is nothing in your sample workbook that matches your final criterion, but try this:

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	INPUT	INDEX	OUTPUT	NEW OUTPUT
2	A1236	6	36	36
3	A2589	8	24	39
4	A3662	2	32	32
5	A1561	1	69	69
6	A1236	4	14	35
7	A3662	3	19	19
8	A2589	1	20	30
9	A3662	3	45	19

Sheet: W1

Excel 2016 (Windows) 32 bit

	D
2	=IFERROR(INDEX('W2'!$C$2:$C$9,MATCH(1,(A2='W2'!$A$2:$A$9)*(B2='W2'!$B$2:$B$9),0)),"Different")

Sheet: W1

... 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.

Please let us know what would be your expected result in your attached file???

Hi

in D2 to be copied down

=SUMPRODUCT('W2'!$C$1:$C$1000,('W2'!$A$1:$A$1000=A2)*('W2'!$B$1:$B$1000=B2))


in E2

=IF(C2=D2,"","DIFFERENT")


Regards

Try in D2 W1:

=IFERROR(LOOKUP(2,1/('W2'!A:A=A2)/('W2'!B:B=B2),'W2'!C:C),"Different")

Or Try

=IF(C2=INDEX('W2'!C:C,MATCH(A2&B2,INDEX('W2'!A:A&'W2'!B:B,),0)),C2,"DIFFERENT")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

It might also be courteous to say 'thank you' to all those who tried to help - you were given four different solutions within a quarter of an hour!

Originally Posted by AliGW

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

It might also be courteous to say 'thank you' to all those who tried to help - you were given four different solutions within a quarter of an hour!

Yep, you are right! Thank you all!

Originally Posted by omid020

It works vey nice but I think this one is better because it does not use arrays and is less complicated

Please Login or Register  to view this content.

Whichever you choose, of course, but all the solutions offered work.

Please don't forget to mark the thread as solved.

You're welcome!