formula not recognising criteria

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

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

Formula not recognising criteria

resolvedResolved · Urgent Priority · Version 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.


 

Excel tip:

How to Spell Check an Excel 2010 Worksheet

Excel 2010 does not automatically spell check a document. So, here's how to manually spell check a worksheet.

Either select the ''Review'' tab in the Ribbon, go to the ''Proofing'' section and click ''Spelling.'' Or, simply press F7.

View all Excel hints and tips


Server loaded in 0.07 secs.