+ Reply to Thread
Results 1 to 6 of 6

lookup tables in cells

  1. #1
    Guest

    lookup tables in cells

    Hi all. I was wondering how to error trap inported data
    that uses one column as a lookup. i.e., that column has
    imported part numbers and the column next to it looks up a
    discription for that part number in another worksheet and
    displays it next to the imported part number. The problem
    is if the part number and discription is not in the lookup
    table, the cell unfortunately reverts to the previous cell
    in the lookup table and displays it. It does not show an
    error or something to indicate no match for that cell part
    number in the discription cell. Any solutions?

  2. #2
    Trevor Shuttleworth
    Guest

    Re: lookup tables in cells

    what formula are you using for the lookup ?

    Regards

    Trevor


    <[email protected]> wrote in message
    news:[email protected]...
    > Hi all. I was wondering how to error trap inported data
    > that uses one column as a lookup. i.e., that column has
    > imported part numbers and the column next to it looks up a
    > discription for that part number in another worksheet and
    > displays it next to the imported part number. The problem
    > is if the part number and discription is not in the lookup
    > table, the cell unfortunately reverts to the previous cell
    > in the lookup table and displays it. It does not show an
    > error or something to indicate no match for that cell part
    > number in the discription cell. Any solutions?




  3. #3
    Brad Gover
    Guest

    Re: lookup tables in cells

    The formula is
    =LOOKUP(A5,Table!$A$1:$A$500,Table!$B$1:$B$500)

    Cell A5 contains a number like 100236.
    Worksheet "Table" cell range contains $A$1:$A$500 part
    numbers in increasing order. Cell range $B$1:$B$500
    contains the discriptions. Everything works fine until a
    part number is imported that has no part number to
    reference in the table. Appriciate any advice.

    >-----Original Message-----
    >what formula are you using for the lookup ?
    >
    >Regards
    >
    >Trevor
    >
    >
    ><[email protected]> wrote in message
    >news:[email protected]...
    >> Hi all. I was wondering how to error trap inported data
    >> that uses one column as a lookup. i.e., that column has
    >> imported part numbers and the column next to it looks

    up a
    >> discription for that part number in another worksheet

    and
    >> displays it next to the imported part number. The

    problem
    >> is if the part number and discription is not in the

    lookup
    >> table, the cell unfortunately reverts to the previous

    cell
    >> in the lookup table and displays it. It does not show

    an
    >> error or something to indicate no match for that cell

    part
    >> number in the discription cell. Any solutions?

    >
    >
    >.
    >


  4. #4
    CLR
    Guest

    Re: lookup tables in cells

    Try adding the last condition "FALSE" to your formula.........
    for example: =VLOOKUP(A1,YourTable,2,FALSE)

    it should then return the real thing or an error........

    Vaya con Dios,
    Chuck, CABGx3


    <[email protected]> wrote in message
    news:[email protected]...
    > Hi all. I was wondering how to error trap inported data
    > that uses one column as a lookup. i.e., that column has
    > imported part numbers and the column next to it looks up a
    > discription for that part number in another worksheet and
    > displays it next to the imported part number. The problem
    > is if the part number and discription is not in the lookup
    > table, the cell unfortunately reverts to the previous cell
    > in the lookup table and displays it. It does not show an
    > error or something to indicate no match for that cell part
    > number in the discription cell. Any solutions?




  5. #5
    Trevor Shuttleworth
    Guest

    Re: lookup tables in cells

    Brad

    the lookup should be:

    =VLOOKUP(A5,Table!$A$1:$B$500,2,FALSE)

    If there is no other data in the columns, you can shorten this to:

    =VLOOKUP(A5,Table!$A:$B,2,FALSE)

    Regards

    Trevor


    "Brad Gover" <[email protected]> wrote in message
    news:[email protected]...
    > The formula is
    > =LOOKUP(A5,Table!$A$1:$A$500,Table!$B$1:$B$500)
    >
    > Cell A5 contains a number like 100236.
    > Worksheet "Table" cell range contains $A$1:$A$500 part
    > numbers in increasing order. Cell range $B$1:$B$500
    > contains the discriptions. Everything works fine until a
    > part number is imported that has no part number to
    > reference in the table. Appriciate any advice.
    >
    >>-----Original Message-----
    >>what formula are you using for the lookup ?
    >>
    >>Regards
    >>
    >>Trevor
    >>
    >>
    >><[email protected]> wrote in message
    >>news:[email protected]...
    >>> Hi all. I was wondering how to error trap inported data
    >>> that uses one column as a lookup. i.e., that column has
    >>> imported part numbers and the column next to it looks

    > up a
    >>> discription for that part number in another worksheet

    > and
    >>> displays it next to the imported part number. The

    > problem
    >>> is if the part number and discription is not in the

    > lookup
    >>> table, the cell unfortunately reverts to the previous

    > cell
    >>> in the lookup table and displays it. It does not show

    > an
    >>> error or something to indicate no match for that cell

    > part
    >>> number in the discription cell. Any solutions?

    >>
    >>
    >>.
    >>




  6. #6
    Aladin Akyurek
    Guest

    Re: lookup tables in cells

    Since the lookup table is sorted in ascending order on its first column,
    the following would allow you exploit that fact...

    =IF(LOOKUP(A5,Table!$A$1:$A$500)=A5,LOOKUP(A5,Table!$A$1:$B$500),"")

    without unexpected return values.

    Brad Gover wrote:
    > The formula is
    > =LOOKUP(A5,Table!$A$1:$A$500,Table!$B$1:$B$500)
    >
    > Cell A5 contains a number like 100236.
    > Worksheet "Table" cell range contains $A$1:$A$500 part
    > numbers in increasing order. Cell range $B$1:$B$500
    > contains the discriptions. Everything works fine until a
    > part number is imported that has no part number to
    > reference in the table. Appriciate any advice.
    >
    >
    >>-----Original Message-----
    >>what formula are you using for the lookup ?
    >>
    >>Regards
    >>
    >>Trevor
    >>
    >>
    >><[email protected]> wrote in message
    >>news:[email protected]...
    >>
    >>>Hi all. I was wondering how to error trap inported data
    >>>that uses one column as a lookup. i.e., that column has
    >>>imported part numbers and the column next to it looks

    >
    > up a
    >
    >>>discription for that part number in another worksheet

    >
    > and
    >
    >>>displays it next to the imported part number. The

    >
    > problem
    >
    >>>is if the part number and discription is not in the

    >
    > lookup
    >
    >>>table, the cell unfortunately reverts to the previous

    >
    > cell
    >
    >>>in the lookup table and displays it. It does not show

    >
    > an
    >
    >>>error or something to indicate no match for that cell

    >
    > part
    >
    >>>number in the discription cell. Any solutions?

    >>
    >>
    >>.
    >>


+ 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