VLOOKUP no longer works in Office 2007 - Excel

Question

Not sure how a feature that worked fine in previous versions no longer works but I figure there must be a new way to do it that I am unaware of.

I have a simple VLOOKUP.

I have a column (Book#1-ColA) of values (suburbs of Australia)

In an other book (Book#2), I have two columns, (Suburbs (ColA) & Postcodes (ColB))

In Book#1-ColB i place the following:

=VLOOKUP(A1, [Book2.xlsx]Sheet1!$A:$B, 2, FALSE)

But it gives me an error: This file version cannot contain formulas that reference cells beyond a worksheet size of 256 columns or 65536 rows.

I have saved the files as .xlsx and as xls and both file versions fail...

Any ideas on this non-descriptive error?

=========== NOTE: I changed the reference to [Book2]Sheet1!$A20000:$B2000 and this didn't work either (assuming that setting the row limit to 20000 was less than 65536!

Answer 1

Try [Book2]Sheet1!$A1:$B2000.

You cannot use column references (e.g. A:B), because the columns are too big, so you need to describe a rectangular range, by specifying two opposite corners.

A2000:B2000 is a range which is one cell high and two wide. You want to specify the whole rectangle, which I assume starts in the top left corner, so it is A1:B2000 (plus all the accoutrements, such as dollar signs and sheet names)

If you have a header row, you should use [Book2]Sheet1!$A2:$B2000.

Answer 2

Though I haven't tried it myself, try saving it as .xlsm (macro-enabled). In my experience with Word 2007, macros don't work if you save it in .doc or .docx. You have to save it in .docm

Answer 3

Try:

VLOOKUP(A1, [Book2.xlsx]Sheet1!$A:$B, 2, FALSE)