+ Reply to Thread
Results 1 to 4 of 4

Troubles with Complex Lookup/INDEX function.... Way over my Head

  1. 08-01-2013, 10:16 AM #1
    gamerofthebeer
    gamerofthebeer is offline
    Registered User
    Join Date
    07-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1

    Troubles with Complex Lookup/INDEX function.... Way over my Head

    Up until recently, I have been the excel guru in my workplace with what I consider basic lookups but for some reason I have really hit a wall on this one. I need to create a lookup system that can lookup a product along with a relevant date within a long list of prices throughout the history of the business. I have attached a sample sheet. Please review and post your assistance. It is greatly appreciated. The price history worksheet that I have at my company is roughly 20,000 line items which makes this impossible without a well constructed formula.

    Thank you for your input ahead of time.Sample.xlsx
    Reply With QuoteRegister To Reply
  2. 08-01-2013, 10:22 AM #2
    GeneralDisarray
    GeneralDisarray is offline
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Troubles with Complex Lookup/INDEX function.... Way over my Head

    Try this in cell C2:

    =SUMPRODUCT(($J$2:$J$12=A2)*(B2>=$L$2:$L$12)*(B2<=$M$2:$M$12)*($K$2:$K$12))/SUMPRODUCT(($J$2:$J$12=A2)*(B2>=$L$2:$L$12)*(B2<=$M$2:$M$12))


    --

    First post was updated. Try this. Note, if the date (column B) is between multiple ranges (L:M columns) then you will get an average of those prices.
    Last edited by GeneralDisarray; 08-01-2013 at 10:33 AM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  3. 08-01-2013, 10:25 AM #3
    TheCman81
    TheCman81 is offline
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Troubles with Complex Lookup/INDEX function.... Way over my Head

    Can you use something like this?

    However with 20K+ rows it may be a bit slow

    Sample(1).xlsx

    updated with an array approach
    Attached Files Attached Files
    Last edited by TheCman81; 08-01-2013 at 10:35 AM.
    Excel Guru in the making

    <----------If the question has been answered to your satisfication please click the Add Repuation star to the left
    Reply With QuoteRegister To Reply
  4. 08-01-2013, 10:26 AM #4
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Troubles with Complex Lookup/INDEX function.... Way over my Head

    one more approach.. with Ctrl + Shift + enter

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by Debraj Roy; 08-01-2013 at 10:30 AM.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...
    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)

Similar Threads

  1. [SOLVED] Complex index/match lookup
    By rinkjames in forum Excel General
    Replies: 4
    Last Post: 06-10-2012, 01:27 PM
  2. Complex array lookup function?
    By hodeware in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-13-2011, 03:41 AM
  3. Excel Lookup with Multiple Queries - Complex Index Match Function
    By Hot Soup in forum Excel General
    Replies: 1
    Last Post: 08-17-2011, 06:33 PM
  4. Excel 2007 : Somewhat complex lookup function
    By IdRatherBeCoding in forum Excel General
    Replies: 3
    Last Post: 06-22-2011, 03:12 PM
  5. A complex Lookup function
    By alexvl in forum Excel General
    Replies: 1
    Last Post: 02-10-2011, 04:54 AM

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