+ Reply to Thread
Results 1 to 6 of 6

VLOOKUP returning wrong cell?

  1. #1
    Dluxe
    Guest

    VLOOKUP returning wrong cell?

    Hi all... I'm sure tihs is a simple VLOOKUP problem, but I can't seem to
    figure out the issue.

    I have a lookup table of Nation abbreviations and the corresponding full
    name:
    IN India
    IO British Indian Ocean Terr
    IP Clipperton Island
    IR Iran
    IS Israel
    IT Italy
    IV Cote D'Ivoire
    IZ Iraq

    On a separate page, I have a listing of nations for people in a program (by
    CODE). I want to use VLOOKUP to take the coded country and return the
    nation name.

    So,
    A B C
    IT [formula] Italy

    I entered the following formula:
    =VLOOKUP(A1,NationLookup,2)

    The formula 'works', but not properly. Using the example above, it would
    return ISRAEL instead of ITALY. It seems to be finding the right code, but
    returning the value from one row up in the lookup table.

    Anyone know how to fix this??

    B



  2. #2
    Peo Sjoblom
    Guest

    Re: VLOOKUP returning wrong cell?

    You need to use an exact match

    =VLOOKUP(A1,NationLookup,2,FALSE)

    or

    =VLOOKUP(A1,NationLookup,2,0)

    --
    Regards,

    Peo Sjoblom

    (No private emails please)


    "Dluxe" <dluxe@gmailDOTcom> wrote in message
    news:[email protected]...
    > Hi all... I'm sure tihs is a simple VLOOKUP problem, but I can't seem to
    > figure out the issue.
    >
    > I have a lookup table of Nation abbreviations and the corresponding full
    > name:
    > IN India
    > IO British Indian Ocean Terr
    > IP Clipperton Island
    > IR Iran
    > IS Israel
    > IT Italy
    > IV Cote D'Ivoire
    > IZ Iraq
    >
    > On a separate page, I have a listing of nations for people in a program
    > (by CODE). I want to use VLOOKUP to take the coded country and return the
    > nation name.
    >
    > So,
    > A B C
    > IT [formula] Italy
    >
    > I entered the following formula:
    > =VLOOKUP(A1,NationLookup,2)
    >
    > The formula 'works', but not properly. Using the example above, it would
    > return ISRAEL instead of ITALY. It seems to be finding the right code,
    > but returning the value from one row up in the lookup table.
    >
    > Anyone know how to fix this??
    >
    > B
    >
    >



  3. #3
    Ron Rosenfeld
    Guest

    Re: VLOOKUP returning wrong cell?

    On Tue, 31 May 2005 10:19:13 -0400, "Dluxe" <dluxe@gmailDOTcom> wrote:

    >Hi all... I'm sure tihs is a simple VLOOKUP problem, but I can't seem to
    >figure out the issue.
    >
    >I have a lookup table of Nation abbreviations and the corresponding full
    >name:
    >IN India
    >IO British Indian Ocean Terr
    >IP Clipperton Island
    >IR Iran
    >IS Israel
    >IT Italy
    >IV Cote D'Ivoire
    >IZ Iraq
    >
    >On a separate page, I have a listing of nations for people in a program (by
    >CODE). I want to use VLOOKUP to take the coded country and return the
    >nation name.
    >
    >So,
    >A B C
    >IT [formula] Italy
    >
    >I entered the following formula:
    >=VLOOKUP(A1,NationLookup,2)
    >
    >The formula 'works', but not properly. Using the example above, it would
    >return ISRAEL instead of ITALY. It seems to be finding the right code, but
    >returning the value from one row up in the lookup table.
    >
    >Anyone know how to fix this??
    >
    >B
    >


    Since your nation abbreviations are in alphabetical order, it is likely that
    what you see in the abbreviation list is not what is really there.

    For example, if you copied this table from an Internet (or html) page, it may
    be that there is a trailing, non-printing space after each two-letter code.

    This would give the behavior you describe and, if you tried to use VLOOKUP but
    look for an exact match, would likely return #N/A.

    Depending on exactly what is in the two-letter code cells, you could:

    1. Clean up the code with a formula approach in a helper column; then
    copy/paste special values back over the original column. Assume K1 has the
    first two letter code, enter the formula and copy/drag down as needed.

    =SUBSTITUTE(TRIM(K1),CHAR(160),"")

    2. Clean up the code with a macro.

    3. Modify the VLOOKUP formula to take into account the extra character(s).
    For example:

    =VLOOKUP(A1&char(160),NationLookup,2)


    --ron

  4. #4
    Dluxe
    Guest

    Re: VLOOKUP returning wrong cell?

    Peo,

    Thanks so much for the response. When I include either the 0 or the FALSE,
    I get back an #N/A error.

    I've tried sorting the lists different ways to see if that makes it
    better... But that doesn't seem to help...

    Am I missing something?

    Thank you again!!
    B
    "Peo Sjoblom" <[email protected]> wrote in message
    news:%[email protected]...
    > You need to use an exact match
    >
    > =VLOOKUP(A1,NationLookup,2,FALSE)
    >
    > or
    >
    > =VLOOKUP(A1,NationLookup,2,0)
    >
    > --
    > Regards,
    >
    > Peo Sjoblom
    >
    > (No private emails please)
    >
    >
    > "Dluxe" <dluxe@gmailDOTcom> wrote in message
    > news:[email protected]...
    >> Hi all... I'm sure tihs is a simple VLOOKUP problem, but I can't seem to
    >> figure out the issue.
    >>
    >> I have a lookup table of Nation abbreviations and the corresponding full
    >> name:
    >> IN India
    >> IO British Indian Ocean Terr
    >> IP Clipperton Island
    >> IR Iran
    >> IS Israel
    >> IT Italy
    >> IV Cote D'Ivoire
    >> IZ Iraq
    >>
    >> On a separate page, I have a listing of nations for people in a program
    >> (by CODE). I want to use VLOOKUP to take the coded country and return
    >> the nation name.
    >>
    >> So,
    >> A B C
    >> IT [formula] Italy
    >>
    >> I entered the following formula:
    >> =VLOOKUP(A1,NationLookup,2)
    >>
    >> The formula 'works', but not properly. Using the example above, it would
    >> return ISRAEL instead of ITALY. It seems to be finding the right code,
    >> but returning the value from one row up in the lookup table.
    >>
    >> Anyone know how to fix this??
    >>
    >> B
    >>
    >>

    >




  5. #5
    Dluxe
    Guest

    Re: VLOOKUP returning wrong cell?

    Ron,

    That was it EXACTLY... Thanks so much.

    I used the TRIM and everything fell into place. I'll have to go back to the
    source (a pull of a validation table in ORACLE) to figure out what was
    causing the problem.

    Thanks again!

    B
    "Ron Rosenfeld" <[email protected]> wrote in message
    news:[email protected]...
    > On Tue, 31 May 2005 10:19:13 -0400, "Dluxe" <dluxe@gmailDOTcom> wrote:
    >
    >>Hi all... I'm sure tihs is a simple VLOOKUP problem, but I can't seem to
    >>figure out the issue.
    >>
    >>I have a lookup table of Nation abbreviations and the corresponding full
    >>name:
    >>IN India
    >>IO British Indian Ocean Terr
    >>IP Clipperton Island
    >>IR Iran
    >>IS Israel
    >>IT Italy
    >>IV Cote D'Ivoire
    >>IZ Iraq
    >>
    >>On a separate page, I have a listing of nations for people in a program
    >>(by
    >>CODE). I want to use VLOOKUP to take the coded country and return the
    >>nation name.
    >>
    >>So,
    >>A B C
    >>IT [formula] Italy
    >>
    >>I entered the following formula:
    >>=VLOOKUP(A1,NationLookup,2)
    >>
    >>The formula 'works', but not properly. Using the example above, it would
    >>return ISRAEL instead of ITALY. It seems to be finding the right code,
    >>but
    >>returning the value from one row up in the lookup table.
    >>
    >>Anyone know how to fix this??
    >>
    >>B
    >>

    >
    > Since your nation abbreviations are in alphabetical order, it is likely
    > that
    > what you see in the abbreviation list is not what is really there.
    >
    > For example, if you copied this table from an Internet (or html) page, it
    > may
    > be that there is a trailing, non-printing space after each two-letter
    > code.
    >
    > This would give the behavior you describe and, if you tried to use VLOOKUP
    > but
    > look for an exact match, would likely return #N/A.
    >
    > Depending on exactly what is in the two-letter code cells, you could:
    >
    > 1. Clean up the code with a formula approach in a helper column; then
    > copy/paste special values back over the original column. Assume K1 has
    > the
    > first two letter code, enter the formula and copy/drag down as needed.
    >
    > =SUBSTITUTE(TRIM(K1),CHAR(160),"")
    >
    > 2. Clean up the code with a macro.
    >
    > 3. Modify the VLOOKUP formula to take into account the extra
    > character(s).
    > For example:
    >
    > =VLOOKUP(A1&char(160),NationLookup,2)
    >
    >
    > --ron




  6. #6
    Ron Rosenfeld
    Guest

    Re: VLOOKUP returning wrong cell?

    On Tue, 31 May 2005 11:09:39 -0400, "Dluxe" <dluxe@gmailDOTcom> wrote:

    >Ron,
    >
    >That was it EXACTLY... Thanks so much.
    >
    >I used the TRIM and everything fell into place. I'll have to go back to the
    >source (a pull of a validation table in ORACLE) to figure out what was
    >causing the problem.
    >
    >Thanks again!
    >


    You are most welcome. I'm glad you've got things under control. Thank you for
    the feedback.


    --ron

+ 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