Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

formula not recognising criteria

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Formula not recognising criteria

Formula not recognising criteria

ResolvedVersion 365

Formula not recognising criteria

I have 4 columns:
A ("Legacy SAQ Completion"): where I have an Index/Match formula to bring me % values from a different data sheet.
B ("SAQ Copy"): where I manually add % values.
C: where I have an IF formula with different criteria to compare A and B.
D (Is it Lapsed?): where I have Index/Match to bring "Yes" / "No" from a different data sheet.

The formula in D is: =IF(AND([@[SAQ (Copy)]]="",[@[Legacy SAQ Completion]]=""),"",IF(OR([@[Legacy SAQ Completion]]=[@[SAQ (Copy)]],AND([@[Legacy SAQ Completion]]=0,[@[Is it lapsed?]]="Yes")),"Ok",IF(OR([@[Legacy SAQ Completion]]="",[@[Legacy SAQ Completion]]<[@[SAQ (Copy)]]),"To check", "To update")))

The formula in A is: =IFERROR(INDEX('New Tracker'!AC:AC,MATCH([@[Sedex ZS]],'New Tracker'!A:A,0))/100," ")

The issue I have is:
When A and B is empty, C should be empty: =IF(AND([@[SAQ (Copy)]]="",[@[Legacy SAQ Completion]]=""),"".
Instead, the results in C tells me "OK".
"OK" should be only when A and B have the same values.
"Update" should be only when A and B have different values.
I have tested and I got only the result in "C" as empty, when I manually delete the content in the cell A (in this case deleting the formula) and in B (even if there is nothing).
Strange enough, if I delete the content in A and does not delete in B, C tells me "Update".

It seems that C considers A and B with a content, even if the results is empty.

I hope you can help.
Many thanks in advance.

RE: Formula not recognising criteria

Hi Cecilia

Looking at your message it looks like you have a space being returned in the formula found in column A

The formula in A is: =IFERROR(INDEX('New Tracker'!AC:AC,MATCH([@[Sedex ZS]],'New Tracker'!A:A,0))/100," ")

This means when you are doing your comparision you need to either remove the space " " in column A formula, change the formula in column C to include the space as currently you are looking for an empty string ""

When A and B is empty, C should be empty: =IF(AND([@[SAQ (Copy)]]="",[@[Legacy SAQ Completion]]=""),""

I hope this helps

Kind Regards

Dennis

RE: Formula not recognising criteria

Many thanks, issue solved!

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.