98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Formula not recognising criteria
Formula not recognising criteria
Resolved · Urgent Priority · Version 365
Cecilia has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
Excel Advanced - For Power Users course
Excel Dashboards for Business Intelligence course
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
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:How to Spell Check an Excel 2010 WorksheetExcel 2010 does not automatically spell check a document. So, here's how to manually spell check a worksheet. |