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

[jyomun]

I have got Excel 2007 Professional recently installed and since then I am not able to use any function which needs me to select an array of cells - like VLOOKUP or MATCH. All other excel functions are working fine.
Is there any particular setting whcih I am missing? Plse help urgently, most of my work is based on such functions...

[NBVC]

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)

[jyomun]

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)

When i start to insert function arguments, i can enter value to ve looked up but when i try to select the lookup array I am not able to select it. Tried using the formal function route also (ie select function and use the selection tabs to select data) but No Go... tried to copy/paste formula above but still No GO

[NBVC]

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".

[jyomun]

The array is changed to a single cell. So cannot take the formula to completion, hence no "error message".

[jyomun]

The array is changed to a single cell. So cannot take the formula to completion, hence no "error message".

Also happens for all workbooks, not any particular one.

[jyomun]

UPDATE:

The problem occurs only when I am working with 2 different files- i.e data to be looked up and lookup array are in 2 different excel files.

If data is in 2 worksheets of same file, Vlookup works fine.

Even more Confused now!

[romperstomper]

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?

[NBVC]

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.

[romperstomper]

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.

[broro183]

hi jyomun,

Another way of checking if they are in the "same instance of excel" (see below) is by clicking on the Excel Toolbar - Windows menu to see a list of the open & visible files in that "excel instance".

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?

Rob

[jyomun]

Tried all of the above. Confirm that when data is in 2 different files they both are in 'same instance'.

[broro183]

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

[NBVC]

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