Using Subtotal to return text?

[deacon10]

Hi all,

I am trying to use a vlookup that references a singular text value that is selected by an autofilter.

Is there any way of getting the Subtotal function return a single text result? I know this works for numbers, so the formula below returns 0.
=SUBTOTAL(9,A2:A5)

If not, is there any method of returning a single text value from a list after autofilter has been applied?

Example data -

A1 - Test1 (result of A2, when A2 filtered)

A2 - Test1
A3 - Test2
A4 - Test2
A5 - Test3

 

[Eric W]

If I understand you right, you can use a helper column to determine which rows are filtered or not. For example:

Book1
	A	B	C	D	E	F
1	Value1	Value2	Filter flag?		Filter value:	Apple
2	1	Apple	1
3	2	Banana	1
4	3	Cherry	1
5	4	Date	1
6	5	Elderberry	1
7
Sheet1

Cell Formulas
Range	Formula
F1	F1	=INDEX(B2:B10,MATCH(1,C2:C10,0))
C2:C6	C2	=SUBTOTAL(3,B2)

And after I filter:

Book1
	A	B	C	D	E	F
1	Value1	Value2	Filter flag?		Filter value:	Banana
3	2	Banana	1
5	4	Date	1
7
Sheet1

Cell Formulas
Range	Formula
F1	F1	=INDEX(B2:B10,MATCH(1,C2:C10,0))
C3,C5	C3	=SUBTOTAL(3,B3)

The helper column can actually be included in the formula, but it makes it a mess.

 

[deacon10]

This is fantastic. Thank you Eric W!