IF & AND not working

[lucianopedralnunes]

Hello. I have a curious case.

On a Excel sheet, trying to use IF & AND.

=IF(AND(SHEET1!C2="Normal";SHEET1!D2="FALSE");"OK";"NOK")
C2 and D2 are "Normal" and "False" filled.

The formula returns me "NOK", but should answer "OK".

If I manually input Normal and False, the formula works. But this sheet is an export for a web tool that I copy and paste to XLSX template with a huge number of formulas.

Is there any caracter codification or somethng else that I need to check?

My SHEET1 has about 2000 lines, and the formula is not working on each one of them.

[#2]

@lucianopedralnunes 

Try this:

=IF(AND(SHEET1!C2="Normal";SHEET1!D2=FALSE);"OK";"NOK")

[#3]

@lucianopedralnunes 

I assume "FALSE" is text, not Boolean False.

Yes, with copy/pasting or importing from Web that could be not printable characters. You may try to apply TRIM(), CLEAN() to formula, but that not always works. Better to have small sample file to check with few values in question.

[#4]

@Hans Vogelaar 

Ok, this worked. The question is, why?

Both cells are text (normal and false are text), why one works with " and the other does not work?

[#5]

@lucianopedralnunes 

I strongly suspect that the FALSE values are Boolean values, not really text.

If they had been text, your formula would have worked.

[#6]

@Hans Vogelaar 

As I understood it works if enter such texts manually, but doesn't work if copy/paste values from Web. Thus instead of poor "False" it could be same word with addition of non-breaking space at the end or like.