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

 

[Aladin Akyurek]

On 2002-04-12 01:56, KnAsTa wrote:
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

Your formula doesn't tell where the range is in the named file, that is, the sheet name is missing. BTW, the corrected formula will work only when the target file is open.

 

[KnAsTa]

Yeah, sorry, i did have the sheet open when i was using the formula. And i still got invalid responses to it. Nothing syntactically wrong, just doesnt work.

 

[robfo0]

wouldnt you need to define your table with more than one column also? or else what is it vlooking up?

an example would be:
=VLOOKUP(A5,$B$5:$C$5,2)

this would take the value in a5, search for it in column B, and return the value in Column C which matched the adjacent value in B (which would be your A5 value, if it exists)
This message was edited by robfo0 on 2002-04-12 02:06

 

[KnAsTa]

Its getting the information from a second sheet, so it it using more than one column. It stores the result in a seperate column in a seperate worksheet.

 

[robfo0]

im not sure if im confused on what vlookup is, or maybe you ....you have your column in the other sheet as $A$1:$A$10. vlookup would take the value from A5 in your first sheet, and find that value in the second sheet in the table you defined as $A$1:$A$10. then you specified column 1, which is the only column in your table....so if it did work, it would return the same value as it started with? wouldnt you need another column, with the values you wanted to look up?

 

[Aladin Akyurek]

On 2002-04-12 02:04, KnAsTa wrote:
Yeah, sorry, i did have the sheet open when i was using the formula. And i still got invalid responses to it. Nothing syntactically wrong, just doesnt work.

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

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

You have to say in which sheet of week1.xls $A$1:$A$10 is. So adjust Sheet1 to suit.

Note that you're using a 1-column table. I assume that's intentional.
This message was edited by Aladin Akyurek on 2002-04-12 02:30

 

[KnAsTa]

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.

 

[robfo0]

oh, maybe it was me that was confused. So whats the purpose of using a 1 column vlookup? it just returns the value it searches for right?

 

[KnAsTa]

robfo0:
We're just testing the function at first, ie trying to understand it. Then use it on larger objectives.