Find name and row number of filtered data.

[Tom.Jones]

Hi,

In a table I filter the data by an item in column A.
How could I see in a cell (say K2) the name of the item after which the filtering is done and in L2 the number of the row on which that item is (only the first row and not the next ones).

Let's say I filter by AAA.
I would like that AAA to appear to me in K2 and let's say that the row with the first AAA item is 42, so in L2 to show me 42.

Thanks.

 

[JamesCanale]

Where columns A,B,C are filtered. When you select AAA, e1 will say AAA, and f1 will say 3. I used a helper column. There is probably a better way. Basically, it's showing the first record of whatever filter you have.

MrExcelPlayground7.xlsx
	A	B	C	D	E	F
1	Thing	helper	Data		BBB	2
2	BBB	2	4
3	AAA	3	5
4	CCC	4	3
5	BBB	5	4
6	BBB	6	5
7	CCC	7	3
8	AAA	8	4
9	AAA	9	5
10	BBB	10	6
11	BBB	11	7
12	CCC	12	8
13	AAA	13	4
14	BBB	14	5
15	BBB	15	6
16	AAA	16	7
17	AAA	17	8
18	BBB	18	5
Sheet26

Cell Formulas
Range	Formula
E1	E1	=INDEX(A1:A18,SUBTOTAL(105,B2:B18))
F1	F1	=SUBTOTAL(105,B2:B18)
B2:B18	B2	=ROW()

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet26!$A$1:$C$18	E1

 

[Tom.Jones]

James!

Thank you very much.