+ Reply to Thread
Results 1 to 13 of 13

VLookUp with Multiple Values

  1. 05-28-2013, 11:06 AM #1
    JangLang
    JangLang is offline
    Registered User
    Join Date
    05-28-2013
    Location
    Texas, U.S.
    MS-Off Ver
    Excel 2003
    Posts
    9

    VLookUp with Multiple Values

    I am trying a VLookUp with multiple lookup values returning one price. Here is the example:

    From data table: I want to look up Year, Item, State (Matching criteria; all are considered primary keys)
    Returning: Price of that item for that year in that state

    Sheet 1: Data table row would look like this: 2013(A1), Football(B1), Texas(C1), $45.99(D1)
    Sheet 2: My VLookup needs to find the 2013(E5)-Football(F5)-Texas(G5) record and plug into (H5) the $45.99 value

    What would this code look like?

    Thanks!
    Reply With QuoteRegister To Reply
  2. 05-28-2013, 11:40 AM #2
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: VLookUp with Multiple Values

    Is it similar to this?
    http://www.excelforum.com/excel-form...le-lookup.html
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
    Reply With QuoteRegister To Reply
  3. 05-28-2013, 11:41 AM #3
    cheal2
    cheal2 is offline
    Forum Contributor
    Join Date
    03-11-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: VLookUp with Multiple Values

    It would be helpful if you posted an example of your data - less confidential information - with an idea of how you want the output to be displayed. The concept of what you want makes sense, but its more a matter of how you are bringing up this information that would be helpful in determining the best method to solve your problem.
    Reply With QuoteRegister To Reply
  4. 05-28-2013, 11:46 AM #4
    powdow
    powdow is offline
    Registered User
    Join Date
    05-20-2013
    Location
    Boston
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: VLookUp with Multiple Values

    I would use SumProduct. Assuming, the data is in range A1:D100, with Year in column A, Sport in B, and State in C, dollar amount in D the SUMPRODUCT formula is,=SUMPRODUCT(--(A1:A100="2013"),--(B1:B100="Football"),--(C1:C100=”Texas”),(D1:D100). Then you would replace with cell references. So when your list in E5:G5 matches a row in A1:C100 it would return the value in the D column.


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 05-28-2013, 11:55 AM #5
    JangLang
    JangLang is offline
    Registered User
    Join Date
    05-28-2013
    Location
    Texas, U.S.
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: VLookUp with Multiple Values

    cheal2: This is what the data would look like:

    2013, Baseball, Kansas, $1.99
    2010, Baseball, Colorado, $2.50
    2013, Football, Texas, $45.99
    2012, Football, Texas, $42.00
    2001, Football, California, $51.50
    2011, Football, Texas, $39.99

    I want to find the first three columns that match and plug the cost into the field containing the formula.

    Powdow: I'll also give that a try. Thanks as well.
    Reply With QuoteRegister To Reply
  6. 05-28-2013, 05:46 PM #6
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: VLookUp with Multiple Values

    Did you check out my link? It seems to be very similar.
    Reply With QuoteRegister To Reply
  7. 05-28-2013, 08:57 PM #7
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: VLookUp with Multiple Values

    Powdow's formula using SUMPRODUCT will work just fine. Unless you are copying the formula across columns or down the column H then the Absolute references can be omitted like this:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    In either formula, adjust the ranges (all ranges must be of equal length) to suit your data.

    Another way to use the formula is to name the ranges for Year, Item, Place and Price. Select the data in column A and in the field to the left of the formula bar type YEAR, repeat for the other columns. The formula then becomes:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  8. 05-29-2013, 09:28 AM #8
    JangLang
    JangLang is offline
    Registered User
    Join Date
    05-28-2013
    Location
    Texas, U.S.
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: VLookUp with Multiple Values

    The formula looks great and I understand the logic behind it, but it's not working. Does it need to be modified to accomodate numeric values? It works great with text and chokes on numeric. Thanks!
    Reply With QuoteRegister To Reply
  9. 05-29-2013, 09:42 AM #9
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: VLookUp with Multiple Values

    The data that you provided is not consistent at all. There are spaces before and after many of the entries. When these spaces have been cleaned out of your data, all the formulae should work. Make sure that your actual data in each cell doesn't have spaces before and/or after the value.

    After cleaning up your sample data, all the formulae worked as they should.
    Reply With QuoteRegister To Reply
  10. 05-29-2013, 09:43 AM #10
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: VLookUp with Multiple Values

    The data that you provided is not consistent at all. There are spaces before and after many of the entries. When these spaces have been cleaned out of your data, all the formulae should work. Make sure that your actual data in each cell doesn't have spaces before and/or after the value.

    After cleaning up your sample data, all the formulae worked as they should.
    Reply With QuoteRegister To Reply
  11. 05-29-2013, 09:44 AM #11
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: VLookUp with Multiple Values

    It looks like the forum has a problem here of creating double entries
    Reply With QuoteRegister To Reply
  12. 05-29-2013, 12:15 PM #12
    JangLang
    JangLang is offline
    Registered User
    Join Date
    05-28-2013
    Location
    Texas, U.S.
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: VLookUp with Multiple Values

    Thanks all! The code is great. I have some crappy data that needs to be cleaned up. Thanks again!
    Reply With QuoteRegister To Reply
  13. 05-29-2013, 12:57 PM #13
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: VLookUp with Multiple Values

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    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)

Tags for this Thread

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