+ Reply to Thread
Results 1 to 3 of 3

Multiple combined lookups across sheets and rows

  1. #1
    Registered User
    Join Date
    01-21-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    19

    Multiple combined lookups across sheets and rows

    Hello all,

    I tried searching for what I'm trying to do, but the buzz words keep bringing up scenarios that aren't fully solving my issue.

    Please see the attached spreadsheet for reference.

    On the 'Analysis' sheet, in cell I5, I am trying to lookup 5 values on the another sheet. It needs to be a multi-part lookup.

    First, I need to look up the sheet that the data will be on. The sheet will be called what is in cell A5. In this case, it is 'SPY'.

    On the 'SPY' sheet, I need to look up the stock's price from the last day in reference, which is found in cell A1 on the 'Analysis' sheet.

    Next, I need to also look up the stock's price from the previous 4 trading sessions, which will be the 4 rows above this.

    Lastly, I need the average of all 5 values.

    Can anyone please help me with this?
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple combined lookups across sheets and rows

    This would be the formula for I5:

    =AVERAGE(OFFSET(INDEX(INDIRECT("'" & $A5 & "'!E:E"), MATCH($A$1, INDIRECT("'" & $A5 & "'!A:A"), 0)-4), , , $I$2, ))
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple combined lookups across sheets and rows

    Thanks for the feedback!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. INDEX and MATCH(?) combined with SUM across multiple sheets
    By jusmightbeokay in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-14-2014, 06:14 AM
  2. [SOLVED] 40K Rows with multiple values combined with duplicates removed
    By jthoogie in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 04-01-2013, 04:59 PM
  3. Combined data from multiple rows if column contains a duplicate value
    By ALexcell47 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-04-2013, 05:37 PM
  4. Replies: 1
    Last Post: 01-13-2011, 02:19 PM
  5. Multiple sheets prefer not to use V/H lookups
    By IsabellaLearning in forum Excel General
    Replies: 7
    Last Post: 11-21-2010, 07:36 AM
  6. More than Multiple Lookups: Conditional Multiple Lookups
    By mohitspamz in forum Excel General
    Replies: 6
    Last Post: 11-01-2009, 03:32 AM
  7. [SOLVED] Lookups over muliple sheets
    By don in forum Excel General
    Replies: 1
    Last Post: 06-04-2006, 03:45 AM

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