Problem with VLOOKUP

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,011
I am hoping for some help here please

In Cell E8 (Which has been named as 'Operator) I have entered the following formula

VLOOKUP(Direct,C:\My Documents\[Proct excel set up.xls]\Sheet1!\Operators,0,1)

My Table is on another workbook called 'Proct excel set up'
The Table range is called 'Operators'

The first cell in my table range has the word 'Direct' in the cell

In data validation, I have set this up for List and in the source I have put the named range of my cell E8....operator

and I simply cant get the formula to work, can anyone see my obvious mistake ???
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Your formula doesn't look right. What happens when you try to enter it? By the way, I'd generate a formula reference to an external book by having the book open and using navigation to generate the formula ( that way you just can't make spelling mistakes )
 

GaryAtWork

Board Regular
Joined
Dec 19, 2005
Messages
83
Am I missing something or do you have it set up to return the answer from column 0?
 

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,011
Hi Glen

Well I might be missing something but how am I able to navigate to another workbook, I have tried it by opening it up but doesnt do a thing.

When I enter the formula it says i have entered an incorrect formula, highlighting part of the file path? Any ideas
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Hi Paul,

have the other workbook open before you start, and when you are creating the formula, after you've typed =VLOOKUP(Direct, then click on the menu command Window and activate the other workbook and select the required range before continuing your formula.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
VLOOKUP(Direct,C:\My Documents\[Proct excel set up.xls]\Sheet1!\Operators,0,1)

OK, a few comments:

1] If your target is a Name called Direct, fine; but if you want to look up the literal string "Direct", you need to put it in quotes.

2] Your workbook reference needs single quotes positioned as: 'C:\My Documents\[Proct excel set up.xls]\Sheet1'!\Operators,0,1)

3] Get rid of the slash prior to Operators

4] The 3rd argument to VLOOKUP must be a positive integer not larger that the number of columns in the 2nd argument, and not smaller than 1. Zero won't cut it.

5] A 4th argument of 1 requires the data in Operators to be sorted ascending and will return approximate matches if no exact match is found.

EDIT Not sure how Data Validation fits in ...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,289
Messages
5,571,323
Members
412,382
Latest member
Langtn02
Top