=Match(Index(Offset(Subtotal()))??????

[btadams]

At the bottom of column D I'm using the Subtotal function to return the min and max in column D (data filtering is applied). I would also like a formula that would return the name associated with the min and max that are in column A.

Any ideas?


Thanks,

 

[macleanb]

There are several ways to find the name (vlookup, index & match combos etc) but what I couldnt tell from the Excel doc, was which if any of them act in the same way as subtotal (ie ignore the hidden rows).

 

[Domenic]

Try the following array formula that needs to be confirmed with CONTOL+SHIFT+ENTER...

=INDEX($A$2:$A$10,MATCH(1,(SUBTOTAL(3,OFFSET($A$2:$A$10,ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10)),0,1)))*($D$2:$D$10=F1),0))

...where F1 contains the minimum value obtained by the SUBTOTAL function. Note that if there's more than one value that equals the minimum value, the formula will return the first occurrence of the name associated with that value.

Hope this helps!

 

[fairwinds]

Hi

Try:

=VLOOKUP(E23,IF(SUBTOTAL(3,INDIRECT("A"&ROW(INDIRECT("2:21")))),A2:B21,""),2,0)

Needs to be confirmed with Ctrl + shift + enter.

 

[macleanb]

Wont both the above still return rows that have been hidden by the filter?

 

[fairwinds]

Wont both the above still return rows that have been hidden by the filter?

No

 

[macleanb]

Apologies Mr Fairwinds - I stand corrected (and humble for not reading your instructions to the end - or understanding the formula)

 

[btadams]

Domenic,
Many Thanks. That does the trick and I was definately following the wrong trail.


Fairwinds,
Couldn't get your formula to work but would like to. I'm assuming E23 contains the max value returned by the Subtotal function and that A2:B21 contains the table or names and values. Correct?

 

[fairwinds]

Correct, but as it s a vlookup values must be in first column and name in second. If that is not the case you need to use index match.