Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: vlookup - again

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have this =VLOOKUP(B17,$A$40:$B$49,2,FALSE) in my sheet. cell b17 has "=a43".(I'm assuming this is the problem) It gives me the error #N/A. how can I format the b17 cell reference in vlookup so it will see it as a cell reference and not text.


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-07 12:44, gumby wrote:
    I have this =VLOOKUP(B17,$A$40:$B$49,2,FALSE) in my sheet. cell b17 has "=a43".(I'm assuming this is the problem) It gives me the error #N/A. how can I format the b17 cell reference in vlookup so it will see it as a cell reference and not text.
    I presume that B17 houses the formula

    =A43

    If so, What houses A43?

    Moreover, is this A43 in the same sheet as your lookup table $A$40:$B$49?


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    You probably want to put the value to be looked up in B17.

    Go to B17 and enter an amount to be looked up. If you enter something that is recorded in the same way as the information in A43:A49, the formula will give the information in the adjacent column (B).

    If you try to lookup something that is not in your lookup range, you will get the message #N/A.

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Maybe you want to try this

    =OFFSET(INDIRECT(B17),,1)

    will return the adjacent cell to the
    right of cell address A43

    Regards,

    Peo Sjoblom

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    cell a43 contains text "chaintech excel"
    I want vlookup to look in cell b17 which has "=a43" then return the data in cell b43

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-07 14:13, gumby wrote:
    cell a43 contains text "chaintech excel"
    I want vlookup to look in cell b17 which has "=a43" then return the data in cell b43
    You don't mean that B17 has "=a43" literally including the double quotes, do you?

    If so, why is that?

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you have "=a43" in b17 your value will be a43.....If you want the text from a43 to be the value in b17 use =a43 without the "" around your formula.....



    Denny

    [ This Message was edited by: kinkyparamour on 2002-04-07 14:32 ]

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no, the entry in b17 does not have quotes
    b17 contains =a43

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I already gave you the solution, you cannot use vlookup..

    =OFFSET(INDIRECT(B17),,1)

    will return what's in B43 if B17 contains a43

    why don't you try it?

    Regards,

    Peo Sjoblom

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-07 14:52, gumby wrote:
    no, the entry in b17 does not have quotes
    b17 contains =a43
    Now, please answer: In which sheet (sheetname) is your

    =VLOOKUP(B17,$A$40:$B$49,2,FALSE)

    B17

    A43

    and

    B43?

Some videos you may like

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
  •