+ Reply to Thread
Results 1 to 7 of 7

Indirect with Match/Index function

  1. #1
    Registered User
    Join Date
    08-17-2012
    Location
    Atlanta
    MS-Off Ver
    Excel 2003
    Posts
    34

    Indirect with Match/Index function

    Hello,

    I am looking for a formula that combines an INDIRECT function (references a different worksheet) and a MATCH/INDEX function.

    My Master sheet has the INDIRECT reference (January, February or March). Each sheet with month names has Match function with desired sequence order. All I want to do is select a month on Master that would automatically go to that sheet and INDEX data based on MATCHed row.

    I want to avoid unnecessary help columns to reduce formulas (workbook is already too big).

    My Master sheet has the desired result (in yellow) based on the reference month.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Indirect with Match/Index function

    Since indirect will slow your performance with an big sheet, I used VBA to get the result.

    See the attached file.

    Please Login or Register  to view this content.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Indirect with Match/Index function

    Indirect and match function.xlsx
    If you can cosider move the last column as firstthe simplest formula way0
    Name your Monthly Data tables using Name Manager as January...
    The use this formula:
    =VLOOKUP(D27,INDIRECT($B$26),2,0)
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Indirect with Match/Index function

    Try these...

    Entered in E27 and copied down:

    =INDEX(INDIRECT(B$26&"!A:A"),MATCH(D27,INDIRECT(B$26&"!C:C"),0))

    Entered in F27. Format as date then copy down:

    =INDEX(INDIRECT(B$26&"!B:B"),MATCH(D27,INDIRECT(B$26&"!C:C"),0))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    08-17-2012
    Location
    Atlanta
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Indirect with Match/Index function

    Thanks Tony....that is exactly what I needed!

  6. #6
    Registered User
    Join Date
    08-17-2012
    Location
    Atlanta
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Indirect with Match/Index function

    I was expecting a VBA answer but I am just learning VBA now...thanks anyway.

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Indirect with Match/Index function

    You're welcome. Thanks for the feedback!

+ 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. Named range lookup using Index/Indirect/Match Function calls in Macro
    By sgopan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2013, 07:51 PM
  2. [SOLVED] Need assistance on nesting an indirect function in a index / match formula
    By ghosters in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2012, 06:00 AM
  3. [SOLVED] Indirect Function with Vlookup vs. Index/Match
    By Jayule in forum Excel General
    Replies: 4
    Last Post: 07-02-2012, 12:01 PM
  4. INDIRECT function together with INDEX MATCH
    By coasterman in forum Excel General
    Replies: 3
    Last Post: 12-09-2011, 11:06 AM
  5. Problem with INDEX, INDIRECT, MATCH, MATCH
    By JO505 in forum Excel General
    Replies: 5
    Last Post: 09-01-2011, 05:51 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1