I have a worksheet using raw data that Excel does not identify as Tables (ie. plainly putting values in adjacent cells), and on another part of my worksheet, formulas doing lookup in that data, like

VLOOKUP(D$1, Config!$C$4:$E$8,2,FALSE)

Things are going perfectly well, until I decide to declare my raw data as a table (Ctrl-T) to get some extra functionality, like auto-formatting and auto table expansion.

Once the table declared, all my formulas find #N/A in that table, whether I reference the search zone as cell reference (Config!$C$4:$E$8) or by its new table name (modified via the name manager).

A clue (I don't know what it's worth) is that the lookup key (D$1 in my formula) and the data keys (leftmost column of Config!$C$4:$E$8) are numeric (values like 2015). Excel seems to use text lookup when inside a table and fail to match 2015 (in the table) with "2015" (from D$1). This is only a clue, I may be wrong on this.

What can I do to start using tables (auto expanding is really useful) without breaking my fomulas?

EDIT : Things break when the lookup key (D$1) starts being part of a table: 2015 is now seen by formulas as "2015"

  • A dirty workaround would be of course to not include D$1 into a table: as it is the table header, I could declare to Excel that my table has no header, thus keeping the first line untouched. – PPC May 12 '15 at 12:04
  • 1
    To test your clue try to substitute the D$1 with a D$1 & "" or a D$1 + 0 if neither is giving results, the issue lies with something else. – sicarius92 May 12 '15 at 14:17

As sicarius92 points out in his comment, dealing with the changed data type by 'casting' it with D$1 + 0 works for me.

Still, it's only a workaround and doesn't explain why putting data in tables magically change the data type.


In Excel 2010 (don't know for sure how it used to work in 2007), transforming a range into a table turns the headers contents into text, independently of the contents. If you really want to have numbers in the headers, you have to mark the cells formatting as "Number" or "General" (or whatever you need) and reinsert the former data, as apparently Excel also disables the check for incompatible data types (and the automatic solution, the little green arrow and its menu).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.