I tried asking this before, but I don't think I explained myself clearly.

On Sheet 1, I have a listing of stores. I have an auto filter on there so I
can filter by Account name and get back a listing of stores who have
purchased a specific product. On Sheet 2, I have all the Account names and
their total number of stores. I'd like to look up what the total number of
stores are for an account. So if on Sheet 1, I filter on ABC Stores and it
filters to A6, but then I choose XYZ Mart instead and it filters to A55. How
can I always tell the formula to look at whatever the results are from my
Auto Filter?

So what do I put in my vlookup? =vlookup(??,sheet2!a:b,2,false)

Try this:

Assume A1 is the column header with filter applied.

The data range is A2:A15.

This array formula** will return the first item from the filtered range:

=INDEX(A2:A15,MATCH(1,(SUBTOTAL(3,OFFSET(A2:A15,RO W(A2:A15)-MIN(ROW(A2:A15)),0,1)))*(A2:A15<""),0))

Then you can use that result in your lookup formula (or you could combine
them into a single formula):

Assume the above formula is entered in A20:

=VLOOKUP(A20,.............)

Or, combined as an array formula** :

=VLOOKUP(INDEX(A2:A15,MATCH(1,(SUBTOTAL(3,OFFSET(A 2:A15,ROW(A2:A15)-MIN(ROW(A2:A15)),0,1)))*(A2:A15<""),0)),......... .........)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Christa" wrote in message
...
I tried asking this before, but I don't think I explained myself clearly.

On Sheet 1, I have a listing of stores. I have an auto filter on there so
I
can filter by Account name and get back a listing of stores who have
purchased a specific product. On Sheet 2, I have all the Account names
and
their total number of stores. I'd like to look up what the total number
of
stores are for an account. So if on Sheet 1, I filter on ABC Stores and it
filters to A6, but then I choose XYZ Mart instead and it filters to A55.
How
can I always tell the formula to look at whatever the results are from my
Auto Filter?

So what do I put in my vlookup? =vlookup(??,sheet2!a:b,2,false)

Thanks!

I had to modify it slightly as a few parentheses were in the wrong spot (not
a huge deal, with that many it's hard to keep track of them). Here's what I
ended up with:

INDEX(A2:A15,MATCH(1,(SUBTOTAL(3,OFFSET(A2:A15,ROW (A2:A15)-MIN(ROW(A2:A15)),0,1))*(A2:A15<"")),0))

"T. Valko" wrote:

Try this:

Assume A1 is the column header with filter applied.

The data range is A2:A15.

This array formula** will return the first item from the filtered range:

=INDEX(A2:A15,MATCH(1,(SUBTOTAL(3,OFFSET(A2:A15,RO W(A2:A15)-MIN(ROW(A2:A15)),0,1)))*(A2:A15<""),0))

Then you can use that result in your lookup formula (or you could combine
them into a single formula):

Assume the above formula is entered in A20:

=VLOOKUP(A20,.............)

Or, combined as an array formula** :

=VLOOKUP(INDEX(A2:A15,MATCH(1,(SUBTOTAL(3,OFFSET(A 2:A15,ROW(A2:A15)-MIN(ROW(A2:A15)),0,1)))*(A2:A15<""),0)),......... .........)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Christa" wrote in message
...
I tried asking this before, but I don't think I explained myself clearly.

On Sheet 1, I have a listing of stores. I have an auto filter on there so
I
can filter by Account name and get back a listing of stores who have
purchased a specific product. On Sheet 2, I have all the Account names
and
their total number of stores. I'd like to look up what the total number
of
stores are for an account. So if on Sheet 1, I filter on ABC Stores and it
filters to A6, but then I choose XYZ Mart instead and it filters to A55.
How
can I always tell the formula to look at whatever the results are from my
Auto Filter?

So what do I put in my vlookup? =vlookup(??,sheet2!a:b,2,false)

a few parentheses were in the wrong spot

Hmmm...

I don't think so but as long as you got it to work that's all that counts!

--
Biff
Microsoft Excel MVP


"Christa" wrote in message
...
Thanks!

I had to modify it slightly as a few parentheses were in the wrong spot
(not
a huge deal, with that many it's hard to keep track of them). Here's what
I
ended up with:

INDEX(A2:A15,MATCH(1,(SUBTOTAL(3,OFFSET(A2:A15,ROW (A2:A15)-MIN(ROW(A2:A15)),0,1))*(A2:A15<"")),0))

"T. Valko" wrote:

Try this:

Assume A1 is the column header with filter applied.

The data range is A2:A15.

This array formula** will return the first item from the filtered range:

=INDEX(A2:A15,MATCH(1,(SUBTOTAL(3,OFFSET(A2:A15,RO W(A2:A15)-MIN(ROW(A2:A15)),0,1)))*(A2:A15<""),0))

Then you can use that result in your lookup formula (or you could combine
them into a single formula):

Assume the above formula is entered in A20:

=VLOOKUP(A20,.............)

Or, combined as an array formula** :

=VLOOKUP(INDEX(A2:A15,MATCH(1,(SUBTOTAL(3,OFFSET(A 2:A15,ROW(A2:A15)-MIN(ROW(A2:A15)),0,1)))*(A2:A15<""),0)),......... .........)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Christa" wrote in message
...
I tried asking this before, but I don't think I explained myself
clearly.

On Sheet 1, I have a listing of stores. I have an auto filter on there
so
I
can filter by Account name and get back a listing of stores who have
purchased a specific product. On Sheet 2, I have all the Account names
and
their total number of stores. I'd like to look up what the total
number
of
stores are for an account. So if on Sheet 1, I filter on ABC Stores and
it
filters to A6, but then I choose XYZ Mart instead and it filters to
A55.
How
can I always tell the formula to look at whatever the results are from
my
Auto Filter?

So what do I put in my vlookup? =vlookup(??,sheet2!a:b,2,false)