# Problem with VLOOKUP

#### Pauljj

##### Well-known Member
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 ???

### 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
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
Am I missing something or do you have it set up to return the answer from column 0?

#### Pauljj

##### Well-known Member
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
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
VLOOKUP(Direct,C:\My Documents\[Proct excel set up.xls]\Sheet1!\Operators,0,1)

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

Replies
2
Views
171
Replies
1
Views
82
Replies
7
Views
299
Replies
0
Views
69
Replies
1
Views
96