Results 1 to 7 of 7

Thread: VLOOKUP MATCH Not returning all results

  1. #1
    Board Regular RJSIGKITS's Avatar
    Join Date
    Apr 2013
    Location
    Taunton, UK
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VLOOKUP MATCH Not returning all results

    Hi Guys.
    I wonder if anyone could help to shed some light on something I'm struggling with here.. I'm trying to do a simple VLOOKUP MATCH as follows:
    On Sheet 'HomeQuote' I have a DV dropdown in cell D15 that the user selects a model, in this example selecting H1.
    ('Home Quote'!$D$15)

    I have a table called 'QtyTable' on sheet 'Costs'. This has the different models in the top headers along C1:O1 (Headers)
    Components are listed in A2:A155, with the quantity of components required for each model listed below the model.

    On sheet 'SOL', I am trying to make a component Qty order list for the specific model selected.
    Column A has the same components listed as per 'QtyTable' I need the quantities to return for the model in column B

    The formula that I have been trying to use in column B on my SOL sheet is:
    Code:
    =IF(A3="","",VLOOKUP(A3,QtyTable,MATCH('Home Quote'!$D$15,QtyTable[#Headers],0)))
    This code is then copied down the rest of the column.

    It seems to work for the first 5 rows, where it does return the correct qty for the model selected, but then everything below this is returning zero's, #NA's and random incorrect quantities in cells that shouldn't have a quantity...

    I've been going round and round in circles with this, and I'm not getting anywhere...

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,671
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VLOOKUP MATCH Not returning all results

    From what you describe i cant see any problem that immediately comes to mind. Certainly cant explain randomness as i cant see that thats possible. In the first row that produces an erroneous result check what the MATCH produces and that the lookup value is in the leftmost column of the lookup table.
    Looking for opportunities

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VLOOKUP MATCH Not returning all results

    You haven't specified the final argument in the vlookup formula, so it's doing an approximate match, try
    =IF(A3="","",VLOOKUP(A3,QtyTable,MATCH('Home Quote'!$D$15,QtyTable[#Headers],0),0))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP MATCH Not returning all results

    It looks like the problem might be the missing exact match.

    =IF(A3="","",VLOOKUP(A3,QtyTable,MATCH('Home Quote'!$D$15,QtyTable[#Headers],0),0))

    edit:- must learn to type faster
    Last edited by jasonb75; Oct 9th, 2019 at 07:26 AM.

  5. #5
    Board Regular RJSIGKITS's Avatar
    Join Date
    Apr 2013
    Location
    Taunton, UK
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP MATCH Not returning all results

    You've cracked it! Thanks!

  6. #6
    Board Regular RJSIGKITS's Avatar
    Join Date
    Apr 2013
    Location
    Taunton, UK
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP MATCH Not returning all results

    Quote Originally Posted by jasonb75 View Post
    edit:- must learn to type faster

    Thanks for your help - and for confirming it!

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VLOOKUP MATCH Not returning all results

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •