+ Reply to Thread
Results 1 to 6 of 6

Index Match with Multiple Criteria and Multiple Columns

  1. #1
    Registered User
    Join Date
    09-18-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    4

    Index Match with Multiple Criteria and Multiple Columns

    I need help creating an index match formula that will pull in multiple rows of data if they fit the criteria.

    I am looking for a formula that would enable me to pull in multiple rows of information if they fit the criteria of "location" and "action type" from the master sheet as displayed in the yellow boxes on the Location 1 sheet.

    Index Match Question.xlsx

    I have managed to create an Index/Match formula that looks at location:
    =INDEX('Master Sheet'!$A$1:$E$103,SMALL(IF('Master Sheet'!$A$2:$A$103='Location 1'!$C$1,ROW('Master Sheet'!$A$2:$A$103)),ROW(1:1)),COLUMN('Master Sheet'!A:A))
    BUT, I am unable to add in the next criteria "Action Type".

    Can you please help me and provide a formula that will enable me to do this.

    I have attached the spreadsheet to this post.

    Any questions please ask!

    Thanks in advance!

  2. #2
    Forum Contributor
    Join Date
    08-07-2015
    Location
    AB, Canada
    MS-Off Ver
    Excel 2013 x64
    Posts
    132

    Re: Index Match with Multiple Criteria and Multiple Columns

    Try the following ARRAY formula in cell A3 (must be entered as you had the previous array formula, using Ctrl+Shift+Enter instead of Enter)

    Please Login or Register  to view this content.
    Let me know if that works for you.

    edit: With a large dataset you may have to increase the "9999" limit I've entered

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

    Re: Index Match with Multiple Criteria and Multiple Columns

    This array formula** entered in A3 on the Location 1 sheet:

    =IFERROR(INDEX('Master Sheet'!A:A,SMALL(IF(('Master Sheet'!$A$2:$A$103=$C$1)*('Master Sheet'!$E$2:$E$103=$E$1),ROW('Master Sheet'!A$2:A$103)),ROWS(A$3:A3))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy across to E3 then down until you get blanks.
    Last edited by Tony Valko; 09-29-2015 at 03:21 PM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    09-18-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    4

    Re: Index Match with Multiple Criteria and Multiple Columns

    Thank you both, you smashed it! Much appreciated!

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

    Re: Index Match with Multiple Criteria and Multiple Columns

    You're welcome. Thanks for the feedback!

  6. #6
    Registered User
    Join Date
    08-25-2015
    Location
    London, England
    MS-Off Ver
    excel 2013
    Posts
    12

    Re: Index Match with Multiple Criteria and Multiple Columns

    cảm ơn nhiều

+ 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] Index/Match with Multiple Criteria and Multiple return values
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2015, 10:27 AM
  2. Replies: 5
    Last Post: 02-06-2015, 08:44 PM
  3. Replace Index Match with VBA - Multiple Criteria - Thousands of rows and columns
    By jaybrd1 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 04-30-2014, 08:39 AM
  4. [SOLVED] Two criteria Index Match on multiple sheets returning multiple values
    By Joak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2014, 10:03 AM
  5. VLookup or Index/match searching rows instead of columns or multiple criteria
    By Groovicles in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-16-2013, 05:00 PM
  6. Excel 2007 : Index, Match, Large Formula: Multiple Criteria, Multiple Ranges
    By SimpleJack in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2013, 08:54 AM
  7. [SOLVED] INDEX+MATCH with multiple criteria across both rows and columns
    By george_k in forum Excel General
    Replies: 3
    Last Post: 10-26-2012, 04:11 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