VLOOKUP function

KnAsTa · Apr 12th, 2002, 02:56 AM

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 · Apr 12th, 2002, 03:01 AM

Quote:

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 · Apr 12th, 2002, 03:04 AM

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 · Apr 12th, 2002, 03:04 AM

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 · Apr 12th, 2002, 03:07 AM

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 · Apr 12th, 2002, 03:12 AM

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 · Apr 12th, 2002, 03:29 AM

Quote:

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 · Apr 12th, 2002, 03:35 AM

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 · Apr 12th, 2002, 03:39 AM

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 · Apr 12th, 2002, 03:40 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 12th, 2002, 02:56 AM	#1
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	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

Apr 12th, 2002, 03:04 AM	#3
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	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.

Apr 12th, 2002, 03:04 AM	#4
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	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 ]

Apr 12th, 2002, 03:07 AM	#5
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	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.

Apr 12th, 2002, 03:12 AM	#6
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	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?

Apr 12th, 2002, 03:35 AM	#8
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	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.

Apr 12th, 2002, 03:39 AM	#9
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	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?

Apr 12th, 2002, 03:40 AM	#10
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	robfo0: We're just testing the function at first, ie trying to understand it. Then use it on larger objectives.