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

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
96
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

Attachments

  • 1.JPG
    1.JPG
    54.8 KB · Views: 5

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,959
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=IFERROR(IF(VLOOKUP(C3,A:B,2,0)=D3,"OK","Change"),"New")
 
Solution

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
96
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,365
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top