VLOOKUP function
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: VLOOKUP function

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com