+ Reply to Thread
Results 1 to 7 of 7

lookups with multiple inputs

  1. #1
    Registered User
    Join Date
    08-19-2009
    Location
    Maineville, OH
    MS-Off Ver
    Excel 2003
    Posts
    4

    lookups with multiple inputs

    First post! Looks like a great forum with lots of excel gurus, just who I need to talk to!

    I have a spreadsheet with a medium-sized data set, and I need to look up values from the large table, and thin the table down to only the relevant values.

    To arrive at each unique value, I will have to match 3 input cells to the first 3 cells of the data table, then copy values from across that specific line to the appropriate cells of my smaller table. I really don't need to sum or do any math in this step, its just a copy from the master table to the smaller table, the math will come later.

    I think I'm attaching the file (remember-first post) so hopefully it will help with what I'm trying to accomplish. Hopefully this isn't a big deal, but I've been pulling my hair out on this all day and I just can't find a solution anywhere that really matches up to this. Any help would be greatly appreciated. Thank you.
    Attached Files Attached Files
    Last edited by NBVC; 08-19-2009 at 03:01 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: lookups with multiple inputs

    Try, in F4:

    =SUMPRODUCT(--($C$46:$C$405=$C4),--($D$46:$D$405=$D4),--($E$46:$E$405=$E4),F$46:F$405)

    copied down and across the table
    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.

  3. #3
    Registered User
    Join Date
    08-19-2009
    Location
    Maineville, OH
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: lookups with multiple inputs

    Works perfect! You are my idol for the month!

    I looked all day for different functions to try, and I wouldn't have ever guessed that a function named SUMPRODUCT would be used like that.

    Thanks a million! I'm really glad I found this forum!

  4. #4
    Registered User
    Join Date
    08-19-2009
    Location
    Maineville, OH
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: lookups with multiple inputs

    Looks like I spoke too soon. It definitely did what I wanted in my sample spreadsheet (simplified considerably from my real one), but I cannot make the same method (with cell definitions adjusted) work in the my 'real' spreadsheet.

    I really don't see what is different formula-wise, I just remapped the cell locations and kept the same foramt (I think). Does the data have to be vertically up-down inline with each other? That's about the only difference I see between this spreadsheet and the example posted before.

    What gives? Can you make it work here (these cell locations are accurate to the 'real' spreadsheet I'm using). If you can get this on to work in this geometry I can use it directly in my real spreadsheet. What are the "--" supposed to do?

    Thanks again.
    Attached Files Attached Files

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: lookups with multiple inputs

    Problem is in last array.

    You wrote: BK$37:CP$396
    and you must write BK$37:BK$396

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: lookups with multiple inputs

    You have wrong range in your final sumrange...

    =SUMPRODUCT(--($BH$37:$BH$396=$CT37),--($BI$37:$BI$396=$CU37),--($BJ$37:$BJ$396=$CV37),BK$37:CP$396)

    should be:

    =SUMPRODUCT(--($BH$37:$BH$396=$CT37),--($BI$37:$BI$396=$CU37),--($BJ$37:$BJ$396=$CV37),BK$37:BK$396)

  7. #7
    Registered User
    Join Date
    08-19-2009
    Location
    Maineville, OH
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: lookups with multiple inputs

    Thanks to both of you for the quick reply. I was hoping that it was something stupid like that. I keep thinking I want to sort the whole data table, and keep selecting the whole thing instead of just the single column at a time....

    First time for me with sorting tables, I've got a long way to go!

    Thanks again for the awesome help!

+ 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