Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Formula Lookup returns wrong value

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The formula Lookup keeps returning the cell value in the row above the one that I want.
    Does anyone know how to fix it?

    Regards

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,647
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-06 05:34, RisingPhoenix wrote:
    The formula Lookup keeps returning the cell value in the row above the one that I want.
    Does anyone know how to fix it?

    Regards
    Care to post the formula that you use along with what you want it to do?

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

    Default


    Could you show your formula?

    For an exact match, include the fourth parameter "false" or "0".

    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    If you do not require an exact match (the fourth parameter is "true"), ensure that the table_array is sorted.

  4. #4
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is what I'm trying to do.
    I have a list extracted from a database contaning username and logonscrip for 5 different domains (file A). I also have a list for every usernamn in a single domain(file B). I need a function to match the username from file B with the properties from file A.

    Ex from file A
    Testuser:T-domain:logon.bat

    The fomula is =LOOKUP(A3;'User&logon'!A:A)

    Can you help me?
    Regards



    [ This Message was edited by: RisingPhoenix on 2002-05-06 06:02 ]

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

    Default



    With Lookup, the information must be sorted.
    complete the syntax for Lookup.
    Did you specify what column of information to return?

    With Vlookup, you can specify the column etc.

    try the complete syntax for Vlookup.
    See above and Help.

    - specify column to lookup
    - specify if you want an exact match "False"
    - specify actual range of information

    Please advise if lets the formula give correct answers.

    [ This Message was edited by: Dave Patton on 2002-05-06 06:38 ]

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank U for your help. I still can't seem to get the correct data back, only the row above the one that I want.

    Regards,

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The range lookup seaches must be sorted (ascending). If the lookup function does not find the value to be looked up, it will return the value just before it.

    It sounds to me like the value you are looking for and the value in your lookup range might be just a bit different (maybe a trailing space?).

    Whenever I do lookups, I like to nest them:

    =IF(LOOKUP(C4,F4:F14,F4:F14)=C4,LOOKUP(C4,F4:F15,G4:G14),"Not Found")

    Where C4 is the value to search for, F4:F14 is the search range and G4:G14 is the return range.

    Hope this helps,

    K

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,647
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-06 05:59, RisingPhoenix wrote:
    This is what I'm trying to do.
    I have a list extracted from a database contaning username and logonscrip for 5 different domains (file A). I also have a list for every usernamn in a single domain(file B). I need a function to match the username from file B with the properties from file A.

    Ex from file A
    Testuser:T-domain:logon.bat

    The fomula is =LOOKUP(A3;'User&logon'!A:A)

    Can you help me?
    Regards

    [ This Message was edited by: RisingPhoenix on 2002-05-06 06:02 ]
    What value is in A3 which figures as lookup value in the formula you posted?


  9. #9
    New Member
    Join Date
    May 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Lookup returns wrong value

    I too am experiencing the vlookup table returning the row above. Hmmm...

  10. #10
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Lookup returns wrong value

    Welcome to the board...

    Post your formula
    Post an example set of data (including lookupvalue and the array to look in).
    Post your expected results based on the example set of data.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

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
  •