Reference the top line of a filtered list
 

I want to be able to reference the top line of a filtered list with the
intention of using it in a vlookup function in another cell. For example, if
col A has a list of tutor groups R1, R2, R3 etc... and I have a vlookup to
match the tutor group to a person, when I filter by tutor group the persons
name is displayed in this other cell.

Reference the top line of a filtered list
 

doctorhappy wrote:
I want to be able to reference the top line of a filtered list with the
intention of using it in a vlookup function in another cell. For example, if
col A has a list of tutor groups R1, R2, R3 etc... and I have a vlookup to
match the tutor group to a person, when I filter by tutor group the persons
name is displayed in this other cell.


=INDEX(A2:A1000,MATCH(1,SUBTOTAL(3,OFFSET(A2:A1000 ,ROW(A2:A1000)-ROW(A2),,1)),0))

This is an array formula. Commit with CTRL+SHIFT+ENTER.

Reference the top line of a filtered list
 

Hi,

This is a great example of the benefits of using range names, suppose you
name your range A, then Glenn's formula becomes

=INDEX(A,MATCH(1,SUBTOTAL(3,OFFSET(A,ROW(A)-ROW(A2),,1)),))

Anytime you find yourself using the same range over and over in one formula
a range name can shorten it.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"doctorhappy" wrote:

I want to be able to reference the top line of a filtered list with the
intention of using it in a vlookup function in another cell. For example, if
col A has a list of tutor groups R1, R2, R3 etc... and I have a vlookup to
match the tutor group to a person, when I filter by tutor group the persons
name is displayed in this other cell.

Reference the top line of a filtered list
 

Hi,

This is a great example of the benefits of using range names, suppose you
name your range A, then Glenn's formula becomes

=INDEX(A,MATCH(1,SUBTOTAL(3,OFFSET(A,ROW(A)-ROW(A2),,1)),))

Anytime you find yourself using the same range over and over in one formula
a range name can shorten it.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"doctorhappy" wrote:

I want to be able to reference the top line of a filtered list with the
intention of using it in a vlookup function in another cell. For example, if
col A has a list of tutor groups R1, R2, R3 etc... and I have a vlookup to
match the tutor group to a person, when I filter by tutor group the persons
name is displayed in this other cell.