+ Reply to Thread
Results 1 to 3 of 3

VLookup formula returning an incorrect result

  1. #1
    Registered User
    Join Date
    02-19-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    10

    VLookup formula returning an incorrect result

    Hi all,

    I am trying to use a VLookup formula to try and help me create a weekly list of data which we use to raise invoices from.

    I start off with a csv spreadsheet which lists many rows of data and for invoicing purposes, one column should contain a First and Last name with a space in between (there are often several rows with the same worker's name in that field). The source database unfortunately generates this as one "word" (ie no spacing) with the last name preceeding the first which is not an acceptable format for our invoices. Another column on this csv spreadsheet is the payroll reference number of the worker on that row.

    To solve this my idea is to generate a 'details' csv report from the source database which has a column with the worker's name in the correct format. I then plan to use a VLookup formula in the Name column of the invoicing spreadsheet, to pull the name in the correct format from the details spreadsheet by using the payroll reference as the criteria (this is the only field which will is shared on the two reports).

    Unfortunately in my sample it works with all but one row - which results in completely the wrong result and I can't figure out why! I've attached a file which has simply taken two columns from the Invoicing spreadsheet (the payroll ref that is being used for the criteria and the resulting name) and the two necessary columns from the source details spreadsheet. Can anyone explain to me why the result for the reference of PAY01 on the left hand table is Edward Brooks, when it should be Paul Rallings? And how to prevent it happening in future?

    thanks for any help in advance

    Charlotte
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    08-23-2009
    Location
    Hampshire, UK
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    328

    Re: VLookup formula returning an incorrect result

    You need to add FALSE to the end of your formula to get an exact match unless your data is sorted.

    eg =VLOOKUP(B3,$D$3:$E$6,2,FALSE)

  3. #3
    Registered User
    Join Date
    02-19-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: VLookup formula returning an incorrect result

    Thank you - such a simple solution! I have used the VLookup formula for quite some years without the final qualifiying part of the formula so it didn't even feature in my thought process as something that could be wrong! I appreciate the help.

+ 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