New bug: VLOOKUP false error warning

Question

Since updating the latest version of Excel 2011 (14.5.9), I now get an error note in every cell with a VLOOKUP formula, both in old files and any new file I make. It says the formula refers to empty cells. This is not true, and in fact the lookup function works perfectly. It's just very annoying to have all those little green triangles and the floating exclamation mark in the cells. Any fix for this?

Answer 1

I see this only when the range specified for the Table_Array argument contains at least 1 blank cell anywhere within the range... it makes no difference which column or row the blank cell is in. IIRC, there is nothing new about this. I checked in Excel 2008 as well as Excel 2016 (Mac) & I see the same behavior in those versions. Excel 2010 (Windows) does the same if the Formula> Error Checking Option for Formulas referring to empty cells is checked.

Answer 2

This is what Bob is referring to. In 2016, there is a similar setup for error check Preferences.

_________

Disclaimer:

The questions, discussions, opinions, replies & answers I create, are solely mine and mine alone, and do not reflect upon my position as a Community Moderator.

Answer 3

No there is no fix for this. It is by design. However, in Excel preferences, Error checking, you can select what types of error(s) if any, you would like to be notified about.