Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered list question
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered list question
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 , "Bob B" <Bob wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered list question
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. "Domenic" wrote: 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 , "Bob B" <Bob wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered list question
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))0,0),0)) In article , "Bob B" wrote: 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. "Domenic" wrote: 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 , "Bob B" <Bob wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered list question
Thanks. It works, I don't understand why it works, but it works!
"Domenic" wrote: 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))0,0),0)) In article , "Bob B" wrote: 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. "Domenic" wrote: 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 , "Bob B" <Bob wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I count items in a filtered list? | Excel Worksheet Functions | |||
Inventory List Question | Excel Discussion (Misc queries) | |||
Custom List Question | Excel Discussion (Misc queries) | |||
Active / Dynamic Filtered List (changes w/ change in cell value) | Excel Discussion (Misc queries) | |||
Drop down list question | New Users to Excel |