+ Reply to Thread
Results 1 to 3 of 3

Index Match.. One step away.

  1. #1
    Registered User
    Join Date
    09-04-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    15

    Index Match.. One step away.

    Trying to get the proper results to show. SHEET ATTACHED.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Index Match.. One step away.

    This is how I would do it. Only one array formula in a helper column that gives the row no and then INDEX for all other columns. This will make it faster and easier to maintain.
    I converted your data into an Excel List, it will expand automatically (including the formulas in it). By using the Excel list the ranges for the array formula is no larger than they have to be. This way it will run much, much faster.

    Check the array formula if I managed to get your new criteria in there.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Index Match.. One step away.

    Another approach, without an array formula...

    on DATA sheet, in a helper column (I used AE), copy this down as far as you need...
    =IF(AND(A2=MASTER!$E$2,E2=MASTER!$G$2,X2>=MASTER!$H$2),1+AE1,AE1)

    Then in MASTER D4, copied down and across, use this...
    =iferror(INDEX(DATA!$A$1:$AD$20,MATCH(ROW($A1),DATA!$AE$1:$AE$20,0),MATCH(MASTER!D$3,DATA!$A$1:$AD$1,0)),"")

    NOTE: Make sure that all of your headings match exactly, else the match wont work
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  2. [SOLVED] For step loop, need step size to change based on reference cell in row
    By Telperion in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-22-2013, 03:41 PM
  3. Replies: 5
    Last Post: 02-29-2012, 08:51 PM
  4. Taking an Index Match Function one step further!
    By Ben Morton in forum Excel General
    Replies: 2
    Last Post: 04-08-2009, 07:17 AM
  5. Index and Match - the next step
    By MoonWeazel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-27-2005, 01:05 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