Capture-Array Formula.PNG
Capture-Subtotal and Max.PNG
Friends
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.
thanks
Bookmarks