How - Match/If/Countif

**Shermaine2010** · 03-31-2013, 09:53 AM

Hi,

Please refer to file attached.

Clause one formula - =IF(AND(COUNTIF($A$5:$A$6,A16)=1,INDEX($B$5:$B$6,MATCH(A16,$A$5:$A$6,0))<>B16),"Check ","True")
Clause two formula - = IF(AND(COUNTIF($A$5:$A$6,A17)<>1,VALUE(MID(B17,4,2))=MONTH($B$1),(VALUE(RIGHT(B17,4))=YEAR($B$1))),"TRUE","Check-Date")

No idea how to combine it together. Please help.

Thanks

**FDibbins** · 03-31-2013, 10:01 AM

I didnt have time to check your file, but could you not just replace the TRUE in the 1st formula with the entire 2nd formula?

=IF(AND(COUNTIF($A$5:$A$6,A16)=1,INDEX($B$5:$B$6,MATCH(A16,$A$5:$A$6,0))<>B16),"Check ","= IF(AND(COUNTIF($A$5:$A$6,A17)<>1,VALUE(MID(B17,4,2))=MONTH($B$1),(VALUE(RIGHT(B17,4))=YEAR($B$1))),"TRUE","Check-Date")")

**Shermaine2010** · 03-31-2013, 11:11 AM

tigertiger - your formula is applicable but I would like to show "Check" or "Check- Date".

**tigertiger** · 03-31-2013, 11:57 AM

Originally Posted by Shermaine2010

tigertiger - your formula is applicable but I would like to show "Check" or "Check- Date".

if that, you use the formula for C15
=IF(COUNTIF($A$5:$A$6,A15)>0,IF(B15=LOOKUP(A15,$A$5:$A$6,$B$5:$B$6),TRUE,"Check"),IF(B15=$B$1,TRUE,"Check-Date"))
copy down for C16,C17,...etc

one time again:
However, plz, you 'd mention about all cell containing date data --- All cells that , except cell B1, contain date as text (not date type) - cause the failure result

**Shermaine2010** · 03-31-2013, 09:30 PM

Originally Posted by tigertiger

if that, you use the formula for C15
=IF(COUNTIF($A$5:$A$6,A15)>0,IF(B15=LOOKUP(A15,$A$5:$A$6,$B$5:$B$6),TRUE,"Check"),IF(B15=$B$1,TRUE,"Check-Date"))
copy down for C16,C17,...etc

one time again:
However, plz, you 'd mention about all cell containing date data --- All cells that , except cell B1, contain date as text (not date type) - cause the failure result

Thanks. Also thanks for the reminder. I am aware that B1 is date format. I use formula to change it. Thanks

**Shermaine2010** · 03-31-2013, 11:13 AM

Originally Posted by FDibbins

I didnt have time to check your file, but could you not just replace the TRUE in the 1st formula with the entire 2nd formula?

=IF(AND(COUNTIF($A$5:$A$6,A16)=1,INDEX($B$5:$B$6,MATCH(A16,$A$5:$A$6,0))<>B16),"Check ","= IF(AND(COUNTIF($A$5:$A$6,A17)<>1,VALUE(MID(B17,4,2))=MONTH($B$1),(VALUE(RIGHT(B17,4))=YEAR($B$1))),"TRUE","Check-Date")")

There is error on formula clause one...
If no MATCH, formula become #N/A .. Formula clause two unable to apply...

**tigertiger** · 03-31-2013, 10:04 AM

use this formula for cell C15:

=IF(COUNTIF($A$5:$A$6,A15)>0,B15=LOOKUP(A15,$A$5:$A$6,$B$5:$B$6),B15=$B$1)

However, plz, you 'd mention about all cell containing date data --- All cells that , except cell B1, contain date as text (not date type) - cause the failure result