| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A list of what is being filtered on? | Excel Discussion (Misc queries) | |||
| How to combine two list on one reference line Excel 2003 | Excel Discussion (Misc queries) | |||
| Totalling up a Filtered List | Excel Worksheet Functions | |||
| creating a filtered list | Excel Discussion (Misc queries) | |||
| How do I reference an Excel filtered list in a function? | Excel Worksheet Functions | |||
Linear Mode
