+ Reply to Thread
Results 1 to 5 of 5

VLOOKUP will not find exact matches, even if the exact same value is in the lookup array

  1. #1
    Registered User
    Join Date
    01-06-2015
    Location
    Dallas, Texas
    MS-Off Ver
    2011 Mac Office
    Posts
    3

    VLOOKUP will not find exact matches, even if the exact same value is in the lookup array

    Hi, I am trying to perform what I thought was a simple lookup of descriptions for SKU numbers. I am using Mac Office 2011. Please see the info and photo below for the details. Any help would greatly appreciated. Thank you.




    wtf_formula.jpg





    FIGURE 1:

    Column A is over 8000 SKU numbers. Column B has the descriptions for that SKU

    Column G is an assorted list of SKU numbers. I want to pull the descriptions for the SKU numbers in G and insert them into column H.

    FIGURE 2:

    Using VLOOKUP to search A3:B8071 for the value in G51. If searching for an exact match (“FALSE”) it will not find the value, even though an exact match is present in the array of data. The example SKU number “71-0287” is present in the array.

    FIGURE 3:

    If you drop the “FALSE” argument and look for an approximate match, it will pull the description from the SKU found just before the one you are looking for. The example SKU 70-5088 is populated by the SKU that comes just before it 70-5087.

    Same results when searching by reference G51 or by the value inside G51 “70-5088”

    No matter what SKU is applied to the formula the results are the same. Except in the few instances where there really is not a match. Both columns are formatted as text, I also tried as numbers. I applied the same theory to a =INDEX MATCH formula. Same results with the TRUE/FALSE argument. Tried the same set of lookup data copied from a excel sheet, and from an SQL query.

    Stuck on stupid for two days messing with this. Any ideas?

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: VLOOKUP will not find exact matches, even if the exact same value is in the lookup arr

    pretty picture but you can't test a formula from an image it is far easier to upload a work book
    just put the bits in that don't work in a new workbook and upload that
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    01-06-2015
    Location
    Dallas, Texas
    MS-Off Ver
    2011 Mac Office
    Posts
    3

    Re: VLOOKUP will not find exact matches, even if the exact same value is in the lookup arr

    The assorted lists of skus will vary in position in the column of 8000+ skus. This is really a scratch workbook anyways. I will upload the entire book to our web server, and then delete it when a solution is found.

    The workbook is 9.8MB, here is the download link.

    http://www.bigdcycle.com/PAGE%20MAKER.xlsx

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: VLOOKUP will not find exact matches, even if the exact same value is in the lookup arr

    there is a space after
    71-0287 in column a so 71-0287(space)<>71-0287
    to remove all trailing spaces try
    select column a
    data /text to columns/fixed width/finish

  5. #5
    Registered User
    Join Date
    01-06-2015
    Location
    Dallas, Texas
    MS-Off Ver
    2011 Mac Office
    Posts
    3

    Re: VLOOKUP will not find exact matches, even if the exact same value is in the lookup arr

    AMAZING!!!!! Thank you.

+ 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. vlookup - not exact matches question
    By tlapointe1970 in forum Excel General
    Replies: 1
    Last Post: 02-20-2014, 02:14 PM
  2. [SOLVED] Array Formula - To determine exact matches
    By [email protected] in forum Excel Formulas & Functions
    Replies: 38
    Last Post: 11-10-2012, 01:17 AM
  3. Lookup with dropdown lists not returning exact matches..
    By Ryleygray in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-20-2012, 01:21 AM
  4. Replies: 0
    Last Post: 08-11-2012, 03:25 PM
  5. Find exact match only using LOOKUP (not VLOOKUP)?
    By paulr24 in forum Excel General
    Replies: 3
    Last Post: 01-27-2012, 03:14 PM

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