#NAME? in VLOOKUP

[HookEm]

My Excel 97 was just upgraded to Excel 2000 - am now having problems with VLOOKUP. Always returns #NAME? Have created basic simple tables and tested and it still does not work. I went to Tools, Options, Calculations and made sure Accept Labels in Formulas is checked. What else can I be missing?

HookEm

 

[Zack Barresse]

that's usually the error when it cannot find your array, or range specified in your formula. were you using named ranges? and are those names still in place?

 

[HookEm]

Yes, they are there. I started off with my label'd range in another worksheet adjacent to the one I was working in. When nothing worked, I moved the range to the same sheet (far off to the right) and still got the error. I then created a simple table of 2 columns, 4 rows, named it Test and populated column a with a,b,c d and column b with 1,2,3 and4. Right next to it I did =VLOOKUP(C1,Test,2,False) and got the same error. I've used VLOOKUP a bunch of times before so I'm fairly confident it's not me - could be but I don't think so. Any ideas?

HookEm

 

[Andrew Poulsom]

What happens if you use it with cell references instead of a named range? Is VLOOKUP available if you select a blank cell and choose Insert|Function?

 

[HookEm]

Just tried it your way and it worked. That's really weird. Any idea whats going on?

HookEm

 

[Andrew Poulsom]

Just tried it your way and it worked. That's really weird. Any idea whats going on?

HookEm

Just tried it what way?

 

[HookEm]

Andrew,
Apologies! Here's what I did: Instead of using my range name I had created, I clicked on the cell, chose Insert, Formula and referenced the cell range instead of my range name. This is what you suggested and it worked perfectly.

Went back to my columns where I originally started having this problem before I started testing - clicked on one of the cells, chose Insert, Formula. It came up with the VLOOKUP paramaters filled except the Name field had a red NAME off to the right which tells me it did not like my range name. Tried again creating a new one and it still gave me the same error. Do you name a range differently in 2000 than in 97? All I did was select my data, then while still highlited I clicked in the Name Box and gave it a name just like I always have before. Absolutely cannot make it work, though.

Stumped.

HookEm

 

[Andrew Poulsom]

Make sure you press Enter after typing the name in the Name box. If you just click away from it nothing happens. It's a pain.

 

[HookEm]

Andrew,
You are right but I assume it accepted it because I can click in the range anywhere and the range name shows up in the name box.

Weird.

HookEm