VLOOKUP function

[KnAsTa]

When i use VLOOKUP functions with a range poiting to an external spreadsheet, it gives me errors and ##VALUE responses in my selected fields.
Here is the function i am using:
=VLOOKUP(A5,week1.xls!$A$1:$A$10,1)
where:
-A5 is the word i am searching
-week1.xls is the external spreadsheet
-$A$1:$A$10 is the range i am using
-1 is the column i am getting the result from

Thanx

 

[robfo0]

ah, i see

 

[Ian Mac]

On 2002-04-12 02:35, KnAsTa wrote:
Thanx Aladin Akyurek,
it worked.

But how do you do it for two columns now?
like searching two columns in an external worksheet for 1 value.

Sorry but I'm unsure of what your trying to achieve.

by two columns do you mean search both columns for the same thing?
or
search the first column in a two column range and return the value/text in the next column?

a coment has been made as to why you're using 1 column:
the reason this was asked is that using =VLOOKUP(What_to_look_for,in_this_range,return_value_from_this_column)
the way you seem to be using it, means that if I type 'Ian' in a cell and return the value from column 1 the return will be 'Ian' which is the same, so I don't see the need to look for a value in the first place?

If however you want to now extented the formula to return what is in column 2 of the range, use:

=VLOOKUP(A5,[week1.xls]Sheet1!$A$1:$B$10,2,0)

doing this you have selected two columns $A$1:$B$10 and specified that you'd like the result to come from col 2.

so if I type 'Ian' in for the lookup_criteria and in the table it has £40000 (the salary I wish I had), it would return £40000 as a result.

Confused

Hope this helps

 

[KnAsTa]

yeah thanx, it was helpful, yet disturbing......

How can we modify what VLOOKUP returns so that it returns a boolean, so i could say:
IF it IS in column 1, do this, else... Because right now it is returning a string from my external spreadsheet, and it wont let me use it.....
Confused....?

 

[KnAsTa]

did anyone understand my previous post? I really need to get this to work, its for an assignment.