vlookup formula
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: vlookup formula

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    My lookup is returning #N/A for some reason

    It's looking up the first number of a 4 digit number. some may start with a number some may start with a letter. the letter comes back ok but the number doesn't

    Here is my formula =vlookup(left(a3,1),ship,2,0)

    Thanks




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

    Default

    Ensure that criteria cell and Lookup range
    are both formatted text before entering
    information.

    =VLOOKUP(LEFT(A3,1),Ship,2,0)

    [ This Message was edited by: Dave Patton on 2002-03-28 19:10 ]

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi lars

    Try:


    =VLOOKUP(IF(ISERROR(LEFT(A3)*1),LEFT(A3),LEFT(A3)*1),Ship,2,FALSE)


    Try also not to form the habit of using Zeros instead of False, it's bad practice. Later when you try and de-bug you will find it hard to determine whether zeros are really zeros or they represent False. It can also lead to problems if you do an Edit Replace for zeros.



  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good point Dave!
    I learnt an additional lesson for no extra charge.

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Yogi

    If you post you mailing address I'll post the invoice

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

    Default

      
    It's looking up the first number of a 4 digit number. some may start with a number some may start with a letter. the letter comes back ok but the number doesn't

    Here is my formula =vlookup(left(a3,1),ship,2,0)



    =VLOOKUP(IF(ISNUMBER(LEFT(A3)+0),LEFT(A3)+0,LEFT(A3)),ship,2,0)

    This assumes that the first column of ship is of mixed data type, that is, consists of numeric and text values.

    Aladin

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