+ Reply to Thread
Results 1 to 7 of 7

VLOOKUP giving random and repeated values?!?

  1. #1
    Registered User
    Join Date
    05-22-2012
    Location
    San Jose, Costa Rica
    MS-Off Ver
    Excel 2007
    Posts
    5

    VLOOKUP giving random and repeated values?!?

    Hello and Good Day,

    I've used VLOOKUP for various jobs over the years, but am now stumped. I have a sheet that accumulates data from bank deposits (I work at a private school in Central America) that will then merge into receipts using Word. The VLOOKUP should search for the Deposit number (found in column C on the attached file) and then send the reciept no. value in column H to another sheet.

    The problem is I don't get any N/A response... instead I just get a (seemingly) random answer from the correct column. In the attached sheet you'll notice my test (other sheets deleted). The K column has four values I did for practice (none are repeated in C column). And the shaded yellow cells in L column should give the correct LOOKUP. But it doesn't.....

    Something interesting I've noticed is that if I do the LOOKUP based on what is in column A (the record number), the LOOKUP works fine. It seems like the problem is with column C values.. I checked for extra spaces or any odd value types and still can't figure anything out.

    Thank you so much for your help

    Regards,

    Aaron Smith
    Attached Files Attached Files
    Last edited by ajasons; 05-22-2012 at 11:49 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: VLOOKUP giving random and repeated values?!?

    Add the 4th parameter to the VLOOKUP to get the Exact matches...

    =VLOOKUP(C4,FR,6,FALSE)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: VLOOKUP giving random and repeated values?!?

    Add the 4th argument to the VLOOKUP()

    =VLOOKUP(C4,FR,6,0) or =VLOOKUP(C4,FR,6,FALSE) to find exact match

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: VLOOKUP giving random and repeated values?!?

    I broke my rule - don't go swimming when there are sharks in the water.

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: VLOOKUP giving random and repeated values?!?

    @ Cutter

    Luckily I had time ... I saw the shark fin .. ..
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  6. #6
    Registered User
    Join Date
    05-22-2012
    Location
    San Jose, Costa Rica
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: VLOOKUP giving random and repeated values?!?

    Thanks.. I've always skipped that argument and never had a problem. I'm honestly not sure why it needed it now.. but the problem is fixed and that's what matters.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: VLOOKUP giving random and repeated values?!?

    Read up on VLOOKUP in the Excel help files to find out when you should or don't need to use that parameter....

+ 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