Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: LOOKUP Error

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Central NY(not the park)
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Again,
    I am stumbling through what I thought to be a simple LOOKUP function,but am having some trouble with 1 number. I reference 2 columns H - Employee Numbers and I - Employee Name. When employye Number is enrered into B3 Employee's Name comes up in C4. Some numbers start with 0 and I had trouble with these at first, but corrected that with cell formatting, which is CUSTOM-000. The name that is giving trouble is 119 whenever this number is entered it gives me the name in the cell above the correct one.??? My function is =LOOKUP(B3,H6:H39,I6:I39) any help would be great.
    THANKS!

  2. #2
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-03 14:57, ssjerauld wrote:
    Hello Again,
    I am stumbling through what I thought to be a simple LOOKUP function,but am having some trouble with 1 number. I reference 2 columns H - Employee Numbers and I - Employee Name. When employye Number is enrered into B3 Employee's Name comes up in C4. Some numbers start with 0 and I had trouble with these at first, but corrected that with cell formatting, which is CUSTOM-000. The name that is giving trouble is 119 whenever this number is entered it gives me the name in the cell above the correct one.??? My function is =LOOKUP(B3,H6:H39,I6:I39) any help would be great.
    THANKS!
    I think from you message When you say you are using lookup, you are actually using vlookup in which case the cause may be the following:

    Your list is probably not quite incremental in which case it picks up the nearest in incremental blocks add the following bit and Excel will only accept Exact matches.

    =VLOOKUP(B3,H6:H39,I6:I39, FALSE)

    If this hasn't done it let us know.

    Hope This Helps
    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-03 14:57, ssjerauld wrote:
    Hello Again,
    I am stumbling through what I thought to be a simple LOOKUP function,but am having some trouble with 1 number. I reference 2 columns H - Employee Numbers and I - Employee Name. When employye Number is enrered into B3 Employee's Name comes up in C4. Some numbers start with 0 and I had trouble with these at first, but corrected that with cell formatting, which is CUSTOM-000. The name that is giving trouble is 119 whenever this number is entered it gives me the name in the cell above the correct one.??? My function is =LOOKUP(B3,H6:H39,I6:I39) any help would be great.
    THANKS!
    If you mean by corrected that with cell formatting, which is CUSTOM-000, corrected it via Format|Cells|Custom, your problem is not over.

    The underlying format of B3 must be the same as that of the lookup range H6:H39. You need to resolve that first. Moreover, The values in H6:H39 must be in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value.

    An alternative would be:

    =INDEX(I6:I39,MATCH(B3,H6:H39,0)

    which also requires that the format issue is resolved.

    [ This Message was edited by: Aladin Akyurek on 2002-05-03 16:21 ]

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Central NY(not the park)
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The INDEX function worked exactly as mine did previously, all numbers work fine except for 119. Both B3 and H6:H39 are formatted the same.
    Scott J.

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Central NY(not the park)
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Addition to: all numbers in H6:H39 are in ascending order, but 119 is the first true 3 digit numer, all previous numbers begin with 0. Any Help??

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

    Default

    On 2002-05-03 15:55, ssjerauld wrote:
    Addition to: all numbers in H6:H39 are in ascending order, but 119 is the first true 3 digit numer, all previous numbers begin with 0. Any Help??
    Yep. 119 has as underlying format number, the rest not. Make all text and be done with it.

    You can make that 119 if it's a value that shows up in B3 text by using

    B3&""

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-05-03 16:22 ]

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
  •