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!

 

Training courses

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.