Using Subtotal to return text within an IF Statement

[deacon10]

Hi there, I am trying to use an IF statement that selects a text value based on what is selected within an Autofilter.

As an example, if 'Selection 1' is filtered in column A, I would like to return the heading from Row 4 where the value of 1 is in either B, C, D or E,

So if 'Selection 1' is filtered, B1 would = "Test1" and, if 'Selection 2' is filtered, I would like "Test2" to be shown in B1 etc

My data is

I have tried using the formula below in B1: -

=IF(SUBTOTAL(3,B5)=1,B4,IF(SUBTOTAL(3,C5)=1,C4,IF(SUBTOTAL(3,D5)=1,D4,IF(SUBTOTAL(3,E5)=1,E4,"N/A"))))

The above works for column B, and incorrectly returns N/A for where columns C,D and E = 1. I have also tried (and dramatically failed) to use a helper column with an Index(Match, but I think this can only return results from the same row and not column.

Can any of you wonderful people out there help?

 

[dnorm]

Hi Deacon

Try this:
In Cell A2 I have created a drop down list of your filter request:-
On the Ribbon click Data then select Data Validation, select List on the drop down and then select cells A5:A8. Click OK.
This will provide a filter option for you and a defined place to reference your formula.

=IF(RIGHT(A2,1)="1",INDEX($B$4:$E$4,MATCH(MAX(B5:E5),B5:E5,0)),IF(RIGHT(A2,1)="2",INDEX($B$4:$E$4,MATCH(MAX(B6:E6),B6:E6,0)),IF(RIGHT(A2,1)="3",INDEX($B$4:$E$4,MATCH(MAX(B7:E7),B7:E7,0)),IF(RIGHT(A2,1)="4",INDEX($B$4:$E$4,MATCH(MAX(B8:E8),B8:E8,0)),""))))

RIGHT(A2,1) is looking at the cell value in A2 and returning the last character "1" as a value. The index and match is looking for the max value based on the the "RIGHT" result.

I hope this helps!