0 as range_lookup dissapears in vlookup formula

[Loulie]

I have a new work computer with Excel 2016. I have often used vlookups with other computers without any problems. With this new excel though, I get a mistake when writing a vlookup with TRUE or FLASE as a range_lookup value. I get the message stating that there is a problem with my formula. If I remove the true or false, the formula works fine (but as TRUE as a default)

When I substitute False with a 0 in the formula, it seems to work at first, but the 0 disappears immediately and I cannot drag the formula down the column as the range lookup is now defaulted to TRUE (empty being TRUE)

If I try with 1 as range lookup, it remains and I can drag formula down no problem. But this is not the result I need!

Therefore I would like help on:

- How can I define excel to accept TRUE and FALSE in the formula

and if I can't and need to use 0 or 1;

-How do I define Excel to keep 0 in the formula

Thanks for any help on this

[alansidman]

This sounds like your Excel software may be corrupted. I would suggest you remove and reload and see if that makes a difference. What you are describing is not normal for any version of Excel.

[Loulie]

Thanks. I will see with IT if they accept to reinstall it.... I'll let you know if they do and if it works.

[Loulie]

Reinstallation did not work. I still have the same issue. Any other ideas? I'm at my wits end... Thanks!

[Gregb11]

Is this happening on EVERY file you try VLOOKUP on? Can you attach the file so we can look at it?
(Maybe try using INDEX/MATCH instead?)

[Loulie]

I found the answer!!!! The "Use system separators" was unchecked in the Advanced Excel Options. Checking this fixed the issue. I can now use TRUE or FALSE, and the 0 no longer disappears, if used. Not sure why the separator prevented me to use these values - even if every part to the formula was detected by the formula wizard. But as long as it works! Hope my solution helps someone else with this issue.

[Gregb11]

Great job in finding that and sharing it with us!