vlookup based on results from autofilter

[Christa]

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)

 

[T. Valko]

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,ROW(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(A2: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)

 

[Christa]

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]

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!