+ Reply to Thread
Results 1 to 10 of 10

vlookup issues & more efficient alternatives

  1. #1
    Registered User
    Join Date
    02-18-2009
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    49

    vlookup issues & more efficient alternatives

    I most commonly use vlookup on text data. Often, this results in returning the wrong information; it seems to default. Also, empty cells are a problem It has occurred to me, given the great (and really fast help) I've found on this site, there may be a better way to do this other than using vlookup.

    I have item names A through Z. I have a table of prices for these items. Currently I have an item name in cells A1, A2 and A3 and a formula that looks up the name in each of those cells and adds the resulting values together. This does not allow me to have no entry in one of the three cells (which logically, returns #NA) and also returns a false answer if the item name input is slightly misspelled.

    Suggestion gratefully accepted.

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

    Re: vlookup issues & more efficient alternatives

    make your vlookup not return n/a
    if(iserror(vlookup()),0,vlookup()) will allow you to sum ok
    but mispelling is a different thing! you could just create a validation list from your table then it will always be spelt the same
    "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
    02-18-2009
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: vlookup issues & more efficient alternatives

    Thanks. How does one create a validation table?

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

    Re: vlookup issues & more efficient alternatives

    well rethinking!
    why do you get n/a is this because of mis spelling or it doesnt exist in your table.?

  5. #5
    Registered User
    Join Date
    02-18-2009
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: vlookup issues & more efficient alternatives

    n/a is doesn't exist in table. Misspelling results in an incorrect value.

    PS Doubtless stupid error, but this is my formula based on you if(iserror usage and it eliminates the n/a but now I get no value returns when text is input into D41, 42 and/or 43.

    =IF(ISERROR(VLOOKUP(D41,Flat!$B$7:$D$67,Flat!$E4)+VLOOKUP(D42,Flat!$B$7:$D$67,Flat!$E4)+VLOOKUP(D43,Flat!$B$7:$D$67,Flat!$E4)),0,(VLOOKUP(D41,Flat!$B$7:$D$67,Flat!$E4)+VLOOKUP(D42,Flat!$B$7:$D$67,Flat!$E4)+VLOOKUP(D43,Flat!$B$7:$D$67,Flat!$E4)))

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,512

    Re: vlookup issues & more efficient alternatives

    A VLOOKUP should not return the wrong result unless you do not have it set up for an exact match (set the last argument to FALSE). Hope that helps.

    Seeing your formula, that is the problem. There is a fourth optional argument that you need to set to FALSE for all your VLOOKUPs e.g.
    Please Login or Register  to view this content.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: vlookup issues & more efficient alternatives

    You could also perhaps revert to another approach

    =SUM(SUMIF(Flat!$B$7:$B$67,D41:D43,INDEX(Flat!$C$7:$D$67,0,Flat!$E4-1)))
    confirmed with CTRL + SHIFT + ENTER

    or possibly

    =SUMPRODUCT(--ISNUMBER(MATCH(Flat!$B$7:$B$67,D41:D43,0)),INDEX(Flat!$C$7:$D$67,0,Flat!$E4-1))

  8. #8
    Registered User
    Join Date
    02-18-2009
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: vlookup issues & more efficient alternatives

    Elegant and simple. The fixes are in as per all your input but I now seem to have one more minor glitch.

    Apologizing in advance for the messiness, here are two versions of my final forumula which almost work;

    In this one, the FALSE statement causes no return if any of the three cells has a misspelling which is what I want but the IF statement causes no return if any of the three cells has no value.

    =IF(ISERROR(VLOOKUP(D41,Flat!$B$7:$D$67,Flat!$E$4,FALSE)+VLOOKUP(D42,Flat!$B$7:$D$67,Flat!$E$4,FALSE)+VLOOKUP(D43,Flat!$B$7:$D$67,Flat!$E$4,FALSE)),0,(VLOOKUP(D41,Flat!$B$7:$D$67,Flat!$E$4,FALSE)+VLOOKUP(D42,Flat!$B$7:$D$67,Flat!$E$4,FALSE)+VLOOKUP(D43,Flat!$B$7:$D$67,Flat!$E$4,FALSE)))

    In this version, the IF statement corrects the above problem in; one or two empty cells still returns the value in the third, however.... now the damn FALSE behaves badly in that if there is a mispelling in any of the three cells, instead of returning no value (thus alerting the user that an typing error has been made), it omitts the value of the misspelled cell and adds the other two so the user can easily duped into the false impression that there are no misspellings.

    =IF(ISERROR(VLOOKUP(E41,Flat!$B$7:$D$67,Flat!$E$4,FALSE)),0,VLOOKUP(E41,Flat!$B$7:$D$67,Flat!$E$4,FALSE))+IF(ISERROR(VLOOKUP(E42,Flat!$B$7:$D$67,Flat!$E$4,FALSE)),0,VLOOKUP(E42,Flat!$B$7:$D$67,Flat!$E$4,FALSE))+IF(ISERROR(VLOOKUP(E43,Flat!$B$7:$D$67,Flat!$E$4,FALSE)),0,VLOOKUP(E43,Flat!$B$7:$D$67,Flat!$E$4,FALSE))

    This is so close but I want cake and eat too. Thanks again in advance.

  9. #9
    Registered User
    Join Date
    02-18-2009
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: vlookup issues & more efficient alternatives

    Quote Originally Posted by DonkeyOte View Post
    You could also perhaps revert to another approach

    =SUM(SUMIF(Flat!$B$7:$B$67,D41:D43,INDEX(Flat!$C$7:$D$67,0,Flat!$E4-1)))
    confirmed with CTRL + SHIFT + ENTER

    or possibly

    =SUMPRODUCT(--ISNUMBER(MATCH(Flat!$B$7:$B$67,D41:D43,0)),INDEX(Flat!$C$7:$D$67,0,Flat!$E4-1))
    The second suggestion seems to work quite well provided you don't put the same item in more than one cell which might actually work ok since that scenerio should never be input twicer but again, is not stupid user proof. - oops, no, doesn't deal with misspelling either.

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

    Re: vlookup issues & more efficient alternatives

    VLOOKUP(D41,Flat!$B$7:$D$67,Flat!$E$4,FALSE) i take it flat!e4 = your column number?
    you arn't going to deal with mis spelling!
    if cell says trouble and you input truoble you will never find a match
    but you could put a conditional format on cell that would say turn it red if one or the other had an error
    how many look ups do you want to sum?
    anyway donkyotes sumproduct should work.
    but its time you posted a workbook to be looked at!
    Last edited by martindwilson; 08-12-2009 at 04:07 PM.

+ 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