+ Reply to Thread
Results 1 to 4 of 4

Vlookup not working properly

  1. 05-15-2008, 02:34 PM #1
    b_motl
    b_motl is offline
    Registered User
    Join Date
    05-15-2008
    Posts
    6

    Vlookup not working properly

    I have a thorough understanding of Vlookups and use IF statement within them, but am having unexplainable troubles.

    my data tab has 100+ rows and about 51 columns. in my vlookups when I ask to return a valus for a cell referenced by "10" it will return a value for a cell refernced say "25"....NO clue why...

    My formula is: "=IF($H$2=2007,VLOOKUP($B14,'Commerce BS -Input'!$A$7:$Q$20,$I$2),IF($H$2=2008,VLOOKUP($B14,'Commerce BS -Input'!$R$7:$AH$20,$I$2),VLOOKUP($B14,'Commerce BS -Input'!$AI$7:$AY$20,$I$2)))vlookup(b12,'Commerce BS -input'!$A$10:$Q110,$h$2)"

    in the above formulas, it works for most of the cells, but for a few of them i get a "N/A" sign and for others it returns the wrong value. I ask for a value associated with "$B14" (which is 10) but it returns a value associated with "$b26" (which is 18)

    Help!!!!
    Last edited by b_motl; 05-15-2008 at 02:36 PM.
    Reply With QuoteRegister To Reply
  2. 05-15-2008, 02:39 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    The items in the first columns of each the Vlookups must be in ascending order if you use them the way you have...

    If they are not... and you want to ensure exact matches, then add a FALSE 4th argument to each Vlookup...

    e.g. VLOOKUP($B14,'Commerce BS -Input'!$A$7:$Q$20,$I$2,FALSE)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 05-15-2008, 02:45 PM #3
    b_motl
    b_motl is offline
    Registered User
    Join Date
    05-15-2008
    Posts
    6
    That did work, thanks!!

    However, the numbers I use in the first column "B" and in the data tab are in assending order.

    Thats why I was so confused why it would jump around and return a wrong value.

    Sometimes it was correct, sometimes it gave me and "N/A" and other times the value was way off...
    Reply With QuoteRegister To Reply
  4. 05-15-2008, 03:12 PM #4
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Firstly you need to decide what you want returned if there isn't an exact match. If the answer to that is "#N/A" or "nothing" then you must have FALSE as a 4th argument, as NBVC says. In that case nothing needs to be sorted.

    It sounds to me as if that's what you need

    If you do want a "closest match", i.e. a match with the greatest value smaller than or equal to the lookup value, then your lookup range must be sorted ascending.

    In this formula:

    =VLOOKUP($B14,'Commerce BS -Input'!$A$7:$Q$20,$I$2)

    the lookup range, i.e. the range that needs to be sorted ascending, is

    'Commerce BS -Input'!$A$7:$A$20
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1