+ Reply to Thread
Results 1 to 6 of 6

Problem with using VLookup formula

  1. #1
    Registered User
    Join Date
    07-24-2011
    Location
    belfast
    MS-Off Ver
    office 365
    Posts
    60

    Wink Problem with using VLookup formula

    Hi Guys

    Problem with using VLookup formula

    In a separate tab (List) i have created a table with a breakdown in Col A (i.e Parts, Test, Labour etc) In Col B I have a list of parts etc

    I have a table and in Col D I want this to auto populate dependant what is selected in Col E,

    so for example if in my main table they select AirBag then this would automatically put (Parts) in Col D, if they select MOT test then this would automatically put (Test) in Col D etc

    I have used the following code
    Please Login or Register  to view this content.
    but this returns #NA in Col D

    Any pointers.........

  2. #2
    Forum Contributor
    Join Date
    10-04-2004
    MS-Off Ver
    2013
    Posts
    148

    Re: Problem with using VLookup formula

    Not quite sure I understand how your data is organized.

    Remember the VLOOKUP only looks in the first column of your search range.

  3. #3
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: Problem with using VLookup formula

    @m.b.g, as the other poster pointed out, your problem could be the way standard VLOOKUP works. there are better ways of fetching data in such cases, one of which is INDEX/MATCH and the other is rearranging data layout. sans both, following is a thread you can read up to figure out how to apply the non-standard approach of VLOOKUP to get desired results for you.

    http://www.excelforum.com/july-compe...d-vlookup.html

    it looks like you have been around this forum for a while; you must have seen how utterly useful it is when people share sample data rather than just a formula with some explanation that supports that formula, while the main plot (data layout in your case) is shrouded.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  4. #4
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Problem with using VLookup formula

    How come we posted in this thread yesterday, with a solution using the index function, I even asked mal.b.graham to post a sample worked sheet, that has even disappeared!!!

    The solution was: =Index(List!$A$1:$A$300,Match($E11,List!$B$1:$B$300,0)). I think that was it!!!

    I am also sure that Ace_XL posted about 4 minutes after me!

    Spooky!!
    Last edited by Kevin UK; 08-27-2012 at 04:17 AM.

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

    Re: Problem with using VLookup formula

    There were problems with the site yesterday. Everything I was able to edit or post during the day was gone by last evening and had to be redone.

  6. #6
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Problem with using VLookup formula

    Hi icestationzbra

    To be fair, he did post a worksheet after I requested, it has vanished due to the problems that the Forum was experiencing yesterday. I downloaded it and responded to mal.b.graham

    Quote Originally Posted by icestationzbra View Post
    @m.b.g, as the other poster pointed out, your problem could be the way standard VLOOKUP works. there are better ways of fetching data in such cases, one of which is INDEX/MATCH and the other is rearranging data layout. sans both, following is a thread you can read up to figure out how to apply the non-standard approach of VLOOKUP to get desired results for you.

    http://www.excelforum.com/july-compe...d-vlookup.html

    it looks like you have been around this forum for a while; you must have seen how utterly useful it is when people share sample data rather than just a formula with some explanation that supports that formula, while the main plot (data layout in your case) is shrouded.

+ 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