+ Reply to Thread
Results 1 to 2 of 2

Vlookup Not pulling correct info

  1. #1
    Registered User
    Join Date
    07-08-2016
    Location
    Pensacola, FL
    MS-Off Ver
    2016
    Posts
    20

    Vlookup Not pulling correct info

    I have been using the attached workbook for months with no issues. All of a sudden the formula on the second tab (invoices) is returning the correct Contractor in column H. I have =IF(B2=0," ",VLOOKUP(B2,'ODP LISTING'!B$4:J$46,9)). I am trying to get it so that the column looks back to the first tab of the workbook (ODP Listing) and pulls the name of the contract listed in column J based on the matching ODP PO # entered. Normally this workbook is protected so that my users only enter the ODP PO #, the invoice amt, and the taxable and nontaxable amounts on the invoices tab and the rest pulls over from the ODP listing tab.

    Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,827

    Re: Vlookup Not pulling correct info

    Your formula omits the optional 4th argument for the VLOOKUP() function (https://support.office.com/en-us/art...8-93a18ad188a1 ). When the 4th argument is omitted, Excel defaults to TRUE, which means that VLOOKUP() uses its "approximate match" algorithm that requires the lookup values in column B to be sorted in ascending order. Your values are not sorted, so erratic results are returned.

    Solutions are to either sort the list by column B in the ODP Listing sheet, or to add FALSE or 0 as the 4th argument in your existing function.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pulling the second correct result from Vlookup.
    By timmtamm in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-29-2015, 04:30 PM
  2. Replies: 15
    Last Post: 11-25-2014, 08:14 AM
  3. VLOOKUP is not Pulling from the Correct Sheet
    By randalbg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-01-2013, 09:46 AM
  4. VLOOKUP & pulling info from tables into Excel quote template
    By Marvelous in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-05-2013, 11:13 AM
  5. vlookup help, pulling info from two sheets and matching
    By putitwhere in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-10-2013, 04:32 PM
  6. Index Match not pulling in correct info
    By jeremiahbret in forum Excel General
    Replies: 1
    Last Post: 02-07-2012, 03:48 PM
  7. Search and find info, if correct info found copy into new worksheet
    By JnrParki in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-13-2011, 11:36 AM

Tags for this Thread

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