Excel 2007 : Not able to select array for VLOOKUP or MATCH or similar functions

That's a strange one.... can you explain a bit more what you mean? Do you get an error? Do you get a result which is wrong? Does the range entry turn into a single cell reference?

Can you type in a range in a Vlookup formula?

What happens if you copy/paste a formula from here:

=Vlookup(A1,X1:Y10,2,FALSE)

So when you copied the formula and pasted, what exactly happened? Did it convert the range to a single cell, did it give an error?

Is this only in a particular workbook/worksheet... or in any workbook?

Can you attach screenshots, etc. to help see the "picture".

Are you sure the files are open in the same instance of Excel? If you open them both from the File-Open dialog, can you do it then?

I was thinking the same thing, but I tested it, and you can't hop to the other workbook opened in another instance to select a range... or even a single reference.... that doesn't seem to be the problem here as I understand it.

You can switch to the other workbook from the function wizard and then if you accidentally clicked a cell when you switched back it would appear to have the described behaviour. It's the only thing I can think of that would affect all workbooks and only when referring to a separate workbook in the formula.

Here are few general questions - I'm just taking a few stabs in the dark really!

- Do you have any macros in the files you are opening, or in your personal.xls file?
- Do you have any non-standard addins open?
- If you press [alt + F11] to open the VBE window, then check Tools - References, are there any References with ticks against them that state "missing"?
- Are there any messages at the bottom left of the excel window, such as "scroll lock" or "extend selection"?
- Are you trying to use [ctrl + pg up] or [ctrl + pg dn] to change sheets with the keyboard while half way through entering the formulae?
- Have all the microsoft patches & Service Packs been applied to your computer since the 2007 excel version was installed?
- What happens if you place a single apostrophe in a cell, then paste/type an example vlookup after the apostrophe, press [enter] to exit the cell (leaving it as text). Then clicking back into the cell & deleting the apostrophe from the left of the text string and pressing [enter].

It's very unlikely that we will spot anything, but could you please upload an example file that is affected?

Rob

If all else fails, try reinstalling the program and get all latest patches and service packs.