+ Reply to Thread
Results 1 to 4 of 4

Index match from multiple worksheets based on dependent cell

  1. #1
    Registered User
    Join Date
    12-23-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    85

    Index match from multiple worksheets based on dependent cell

    Hey all,

    I have been looking through the forums, and found a few examples of this problem, but nothing I've been able to make work so far because of the dependent cell. I have three worksheets (Segment_1, Segment_2, Segment_3). Each worksheet has a set of unique IDs on them. I need to be able to look up the segment that has been selected on a fourth tab, and based on that value, pull all of the ID numbers and associated names.

    I have attached a document and wrote an example of the problem on it. There is another problem included in the file as well that does something similar but the other way. Based on the multiple testing tabs, it looks up which ones contain the ID numbers and returns the average values.

    Thanks,
    B
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Index match from multiple worksheets based on dependent cell

    This should achieve column B result.

    Not sure what problem 2 is

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back

    Please also mark your thread as solved once it has been.

  3. #3
    Registered User
    Join Date
    12-23-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Index match from multiple worksheets based on dependent cell

    Hey Dave,

    Thanks for the response. It would be a tough one to implement on my actual document because I have hundreds of "segments", so the If function would be a bit ridiculous. I ended up using this which seems to work:

    =OFFSET(INDIRECT("'" & "Segment_" & $C$3 & "'!B8"), ROWS($B$6:$B6)-1, COLUMNS($B$6:B$6)-1)

    Thanks again for the help though!

    B

  4. #4
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Index match from multiple worksheets based on dependent cell

    Quote Originally Posted by BWellman View Post
    Hey Dave,

    Thanks for the response. It would be a tough one to implement on my actual document because I have hundreds of "segments", so the If function would be a bit ridiculous. I ended up using this which seems to work:

    =OFFSET(INDIRECT("'" & "Segment_" & $C$3 & "'!B8"), ROWS($B$6:$B6)-1, COLUMNS($B$6:B$6)-1)

    Thanks again for the help though!

    B
    Good choice, I was looking at something for INDIRECT but with a small sample I opted against. Now knowing you have numerous options you've nailed it

+ 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. [SOLVED] How to use Index and Match based on multiple criteria using multiple worksheets
    By MariMano in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-15-2017, 09:01 AM
  2. [SOLVED] IF and INDEX/MATCH combo with relative/dependent cell value
    By Nina579 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-14-2016, 10:02 AM
  3. [SOLVED] Index/Match dependent with range dependent on another column
    By golden2282 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2016, 01:12 AM
  4. INDEX and MATCH multiple worksheets
    By bluefiesta in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-30-2014, 03:17 PM
  5. Replies: 2
    Last Post: 01-14-2014, 08:31 PM
  6. Index and Match across multiple worksheets
    By GTHORE in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-09-2013, 01:05 PM
  7. Excel 2007 : Index and Match using multiple worksheets
    By lola12345 in forum Excel General
    Replies: 1
    Last Post: 10-27-2011, 03:31 PM

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