+ Reply to Thread
Results 1 to 2 of 2

Index/Match for Filtered result

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2013

    Index/Match for Filtered result

    Capture-Array Formula.PNG
    Capture-Subtotal and Max.PNG


    Question: I wish to use Index/Match for only the filtered result in a table
    OR in other words: Index/Match for only the visible cells

    Specific scenario:

    I have a table A3:C118 and I am filtering the data and finding the subtotal for the maximum value in column C. How do I find the corresponding data point for column A?
    Row2: Contains headers
    Cell E1:Contains subtotal to find maximum value in visible cells
    Cell F1:Contains Array formula
    Formula in cell E1:=SUBTOTAL(4,C2:C10)
    Formula in Cell F1:=INDEX(A2:A10,MATCH(1,IF(SUBTOTAL(3,OFFSET(C2:C10,ROW(C2:C10)-ROW(C2),0,1)),IF(C2:C10=E1,1)),0))
    Note: Formula in Cell F1 should be entered with Control+Shift+Enter as it is an Array Formula

    I need help in:
    Found this formula in a formula guidebook maintained by the team here.
    It works perfectly fine.
    However I do not understand it. Can anyone help in understanding this?

    Sorry I have problems in attaching the workbook, hence the attached pictures.


  2. #2
    Registered User
    Join Date
    The Netherlands
    MS-Off Ver

    Re: Index/Match for Filtered result


    You can find some info about Array Formulas at http://www.utteraccess.com/wiki/inde...Array_Formulas

    Hoop this helps a bit.
    The Netherlands

+ 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 - More than 1 Result
    By LJenny in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-13-2015, 11:08 AM
  2. index/match based on filtered list
    By wilfrid147 in forum Excel General
    Replies: 8
    Last Post: 08-06-2015, 02:38 PM
  3. Sort filtered results of INDEX/MATCH from drop down using 3 criteria
    By lorikgator in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-13-2015, 05:33 PM
  4. [SOLVED] Index/Match result should again search for zero result
    By ursanil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2014, 04:45 AM
  5. Index Match Returning Results for Filtered Out Data
    By hermes980 in forum Excel General
    Replies: 2
    Last Post: 08-27-2014, 12:19 AM
  6. index match, ..... skip result if blank value ...... jump to next match
    By gehawk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 04:42 AM
  7. [SOLVED] Index Match if result is not something
    By Jaron_t in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2012, 01:19 PM

Tags for this Thread


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