VLOOKUP no longer works in Office 2007 - Excel

Not sure how a fe开发者_高级运维ature 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!

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.

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

Try:

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

I have Office 365 and it works for me, but once only. If I do a lookup in file1 from file2, specifying a column range is accepted for the first time and then rejected with this same explanation. Then, if I close all open Excel files and re-open what I need, it works once again. Hope this helps.