Not sure how to put this formula together Match And not equal to

[Dave01]

I am trying to compare data in 4 columns the conditions are

If the number in column C3 matches column C, and the date in Column D3 matches column B, then ok
If the number in column C3 matches column C and the date in column D3 does not <> col B, change
If the number in column C3 does not match C, then New

=IFERROR(IF(AND(MATCH(C4,A:A,0)*MATCH(D4,B:B,0)),"Match",),"New")

(the change is missing as it either causes errors or it just gets ignored)

I was playing around with this but I cant get the second condition to work, which is if if the numbers match and the the dates does not then change

then this needs to go into one forrmula.

The picture colours are

Green matches
Red is a change
Yellow is new

(colours are not a factor and wont be used, just a way of highlighting them
the red one is wrong, should be change, this is where im stuck.


Could anyone help me resolve this.

thanks

Dave

 

[jasonb75]

This is going off of your test formula and screen capture, there are parts where the description that you provided doesn't match the rest.

=IFERROR(IF(VLOOKUP(C3,A:B,2,0)=D3,"OK","Change"),"New")

 

[Dave01]

Perfect thank you, I havnt used Vlookup in years as I went for the index match, so didnt think to use it, (actually forgotten how to use it.

thanks for your help

dave