+ Reply to Thread
Results 1 to 5 of 5

vlookup

  1. #1
    Guest

    vlookup

    How can I make this work?
    I have a worksheet. I need to extract the first two digits of a number. The
    two digit number determines which equipment to use.That number checks
    against a vlookup table and then the equipment is entered in another cell.
    The worksheet is much larger than this but this should give you an idea what
    the problem is.

    Example:
    A1 = 8110332
    B1 =LEFT(A1,2) B1 now shows 81
    C1 =VLOOKUP(B1,D1:E1,2,FALSE)
    D1 =81
    E1 =Thingy

    The #N/A error shows. I just need to enter the number in A1 and all the rest
    is done for me. I know the problem rests with the formula in B1.



  2. #2
    Peo Sjoblom
    Guest

    Re: vlookup

    Try

    =VLOOKUP(--B1,D1:E1,2,FALSE)

    or in B1 use

    =--LEFT(A1,2)

    then use your vlookup as originally stated

    all the text functions return a text string


    --

    Regards,

    Peo Sjoblom

    http://nwexcelsolutions.com




    <sycewm@sbcglobal.net> wrote in message
    news:fzx3g.63869$F_3.39103@newssvr29.news.prodigy.net...
    > How can I make this work?
    > I have a worksheet. I need to extract the first two digits of a number.
    > The two digit number determines which equipment to use.That number checks
    > against a vlookup table and then the equipment is entered in another cell.
    > The worksheet is much larger than this but this should give you an idea
    > what the problem is.
    >
    > Example:
    > A1 = 8110332
    > B1 =LEFT(A1,2) B1 now shows 81
    > C1 =VLOOKUP(B1,D1:E1,2,FALSE)
    > D1 =81
    > E1 =Thingy
    >
    > The #N/A error shows. I just need to enter the number in A1 and all the
    > rest is done for me. I know the problem rests with the formula in B1.
    >




  3. #3
    Max
    Guest

    Re: vlookup

    > B1 =LEFT(A1,2) B1 now shows 81
    > C1 =VLOOKUP(B1,D1:E1,2,FALSE)


    Try instead in B1: =LEFT(A1,2)+0

    The "+0" will coerce the text number
    returned by LEFTinto a real number
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "sycewm@sbcglobal.net" wrote:
    > How can I make this work?
    > I have a worksheet. I need to extract the first two digits of a number. The
    > two digit number determines which equipment to use.That number checks
    > against a vlookup table and then the equipment is entered in another cell.
    > The worksheet is much larger than this but this should give you an idea what
    > the problem is.
    >
    > Example:
    > A1 = 8110332
    > B1 =LEFT(A1,2) B1 now shows 81
    > C1 =VLOOKUP(B1,D1:E1,2,FALSE)
    > D1 =81
    > E1 =Thingy
    >
    > The #N/A error shows. I just need to enter the number in A1 and all the rest
    > is done for me. I know the problem rests with the formula in B1.


  4. #4
    Guest

    Re: vlookup

    Thank You So Much. Exactly What I Wanted.


    "Max" <demechanik@yahoo.com> wrote in message
    news:AE3C9EA8-048F-40EB-91FE-83EF13233312@microsoft.com...
    >> B1 =LEFT(A1,2) B1 now shows 81
    >> C1 =VLOOKUP(B1,D1:E1,2,FALSE)

    >
    > Try instead in B1: =LEFT(A1,2)+0
    >
    > The "+0" will coerce the text number
    > returned by LEFTinto a real number
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
    > "sycewm@sbcglobal.net" wrote:
    >> How can I make this work?
    >> I have a worksheet. I need to extract the first two digits of a number.
    >> The
    >> two digit number determines which equipment to use.That number checks
    >> against a vlookup table and then the equipment is entered in another
    >> cell.
    >> The worksheet is much larger than this but this should give you an idea
    >> what
    >> the problem is.
    >>
    >> Example:
    >> A1 = 8110332
    >> B1 =LEFT(A1,2) B1 now shows 81
    >> C1 =VLOOKUP(B1,D1:E1,2,FALSE)
    >> D1 =81
    >> E1 =Thingy
    >>
    >> The #N/A error shows. I just need to enter the number in A1 and all the
    >> rest
    >> is done for me. I know the problem rests with the formula in B1.




  5. #5
    Max
    Guest

    Re: vlookup

    You're welcome ! Glad it helped.
    See also Peo's reply for other ways to do it
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    <sycewm@sbcglobal.net> wrote in message
    news:5gy3g.19862$tN3.6380@newssvr27.news.prodigy.net...
    > Thank You So Much. Exactly What I Wanted.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1