software

I want to autofilter a list and then run a vlookup based on the criteria
used. How do you reference the first cell of a filtered list?

Assumptions:

A2:C10 contains your data

E1 contains your lookup value

You wish to return the corresponding value in Column C

Formula:

=VLOOKUP(E1,IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10 )-MIN(ROW(A2:A10)),0,1)
),A2:C10),3,0)

....confirmed with CONTROL SHIFT ENTER, not just ENTER. Adjust the
formula accordingly.

Hope this helps!

In article gt;,
quot;Bob Bquot; lt;Bob gt; wrote:

gt; I want to autofilter a list and then run a vlookup based on the criteria
gt; used. How do you reference the first cell of a filtered list?

Domenic,
How do I get E1 (lookup value) to = the filtered criteria? In your
assumption, say that A contains the month you wish to filter. I need my
lookup value to = whichever month you selected to filter. Sorry for not
being clear.

quot;Domenicquot; wrote:

gt; Assumptions:
gt;
gt; A2:C10 contains your data
gt;
gt; E1 contains your lookup value
gt;
gt; You wish to return the corresponding value in Column C
gt;
gt; Formula:
gt;
gt; =VLOOKUP(E1,IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10 )-MIN(ROW(A2:A10)),0,1)
gt; ),A2:C10),3,0)
gt;
gt; ....confirmed with CONTROL SHIFT ENTER, not just ENTER. Adjust the
gt; formula accordingly.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;Bob Bquot; lt;Bob gt; wrote:
gt;
gt; gt; I want to autofilter a list and then run a vlookup based on the criteria
gt; gt; used. How do you reference the first cell of a filtered list?
gt;

Is this what you mean?

=INDEX(A2:A10,MATCH(TRUE,INDEX(SUBTOTAL(3,OFFSET(A 2:A10,ROW(A2:A10)-MIN(R
OW(A2:A10)),0,1))gt;0,0),0))

In article gt;,
quot;Bob Bquot; gt; wrote:

gt; Domenic,
gt; How do I get E1 (lookup value) to = the filtered criteria? In your
gt; assumption, say that A contains the month you wish to filter. I need my
gt; lookup value to = whichever month you selected to filter. Sorry for not
gt; being clear.
gt;
gt; quot;Domenicquot; wrote:
gt;
gt; gt; Assumptions:
gt; gt;
gt; gt; A2:C10 contains your data
gt; gt;
gt; gt; E1 contains your lookup value
gt; gt;
gt; gt; You wish to return the corresponding value in Column C
gt; gt;
gt; gt; Formula:
gt; gt;
gt; gt; =VLOOKUP(E1,IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10 )-MIN(ROW(A2:A10)),0,1)
gt; gt; ),A2:C10),3,0)
gt; gt;
gt; gt; ....confirmed with CONTROL SHIFT ENTER, not just ENTER. Adjust the
gt; gt; formula accordingly.
gt; gt;
gt; gt; Hope this helps!
gt; gt;
gt; gt; In article gt;,
gt; gt; quot;Bob Bquot; lt;Bob gt; wrote:
gt; gt;
gt; gt; gt; I want to autofilter a list and then run a vlookup based on the criteria
gt; gt; gt; used. How do you reference the first cell of a filtered list?
gt; gt;

Thanks. It works, I don't understand why it works, but it works!

quot;Domenicquot; wrote:

gt; Is this what you mean?
gt;
gt; =INDEX(A2:A10,MATCH(TRUE,INDEX(SUBTOTAL(3,OFFSET(A 2:A10,ROW(A2:A10)-MIN(R
gt; OW(A2:A10)),0,1))gt;0,0),0))
gt;
gt; In article gt;,
gt; quot;Bob Bquot; gt; wrote:
gt;
gt; gt; Domenic,
gt; gt; How do I get E1 (lookup value) to = the filtered criteria? In your
gt; gt; assumption, say that A contains the month you wish to filter. I need my
gt; gt; lookup value to = whichever month you selected to filter. Sorry for not
gt; gt; being clear.
gt; gt;
gt; gt; quot;Domenicquot; wrote:
gt; gt;
gt; gt; gt; Assumptions:
gt; gt; gt;
gt; gt; gt; A2:C10 contains your data
gt; gt; gt;
gt; gt; gt; E1 contains your lookup value
gt; gt; gt;
gt; gt; gt; You wish to return the corresponding value in Column C
gt; gt; gt;
gt; gt; gt; Formula:
gt; gt; gt;
gt; gt; gt; =VLOOKUP(E1,IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10 )-MIN(ROW(A2:A10)),0,1)
gt; gt; gt; ),A2:C10),3,0)
gt; gt; gt;
gt; gt; gt; ....confirmed with CONTROL SHIFT ENTER, not just ENTER. Adjust the
gt; gt; gt; formula accordingly.
gt; gt; gt;
gt; gt; gt; Hope this helps!
gt; gt; gt;
gt; gt; gt; In article gt;,
gt; gt; gt; quot;Bob Bquot; lt;Bob gt; wrote:
gt; gt; gt;
gt; gt; gt; gt; I want to autofilter a list and then run a vlookup based on the criteria
gt; gt; gt; gt; used. How do you reference the first cell of a filtered list?
gt; gt; gt;
gt;