Forum Discussion
Why is my formula not working correctly
I have used a formula to find items over $1,000 and not in a session and while it finds some items correctly its still finding items that are on the sessions and still displaying as not on a session. In col E if its Yes it shouldn't have anything in col F. If you scroll down you will see a few items that have a YES in col E and "Above $1,000...." in col F. Why is that ? What is wrong with my formula.
Here is the formula is col F:
=IF(D48<=1000,"",IF(AND(D48>1000,NOT(ISNA(MATCH(C48,Sessions!$C$3:$C$1000,0)))),"","Above $1,000 and not in a Session"))
Have the data been imported from the web or from another application? There are some hidden characters that MATCH stumbles over.
I ran Text to Columns on the invoice numbers on the Sessions and Data - ACH sheets. This caused the problem to go away.
- When you can the text to column it removed any preceding zeros in an invoice number which will also make the same mistake because the data on the other tabs should have the preceding zeros.
Then you'll have to clean the data manually - not a pleasant task...
- The data from the ACH tab and Sessions tab are both copied from another application and pasted. Is there a specific way i should be coping and pasting this information to avoid the hidden characters?
- When you can the text to column it removed any preceding zeros in an invoice number which will also make the same mistake because the data on the other tabs should have the preceding zeros.
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
