Problem with VLOOKUP

[Pauljj]

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

 

[GlennUK]

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]

Am I missing something or do you have it set up to return the answer from column 0?

 

[Pauljj]

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]

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]

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