Home |
Search |
Today's Posts |
#1
|
|||
|
|||
lookup value froma filtered list
Is there a worksheet function that can lookup the first value in a column in
a list that has been filtered? In all cases the value will have been part of the filter, but done through an autofilter (not an advanced filter). Thanks, Eric |
#2
|
|||
|
|||
The function VLOOKUP should be able to do that whether the list / value is
filtered or not. "Eric" wrote: Is there a worksheet function that can lookup the first value in a column in a list that has been filtered? In all cases the value will have been part of the filter, but done through an autofilter (not an advanced filter). Thanks, Eric |
#3
|
|||
|
|||
There is not a function which will do it directly, but there are ways of
doing what you want if I am reading your question properly. to find the row =min(if(range<filter criteria,1e6,row(range)) entered as array (Control-shift-enter) will find the first row which would not be filtered out "Eric" wrote: Is there a worksheet function that can lookup the first value in a column in a list that has been filtered? In all cases the value will have been part of the filter, but done through an autofilter (not an advanced filter). Thanks, Eric |
#4
|
|||
|
|||
Assuming that B1 contains your header, and B2:B11 contains your data,
try... =INDEX(B2:B11,MATCH(1,(SUBTOTAL(3,OFFSET(B2:B11,RO W(B2:B11)-MIN(ROW(B2:B1 1)),0,1)))*(B2:B11<""),0)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. To include formula blanks "", change... (B2:B11<"") ....to... (1-ISBLANK(B2:B11)) Hope this helps! In article et, "Eric" wrote: Is there a worksheet function that can lookup the first value in a column in a list that has been filtered? In all cases the value will have been part of the filter, but done through an autofilter (not an advanced filter). Thanks, Eric |
#5
|
|||
|
|||
Wow - that's some formula! I'll try it after I get over my headache from
looking at it. Thanks! "Domenic" wrote in message ... Assuming that B1 contains your header, and B2:B11 contains your data, try... =INDEX(B2:B11,MATCH(1,(SUBTOTAL(3,OFFSET(B2:B11,RO W(B2:B11)-MIN(ROW(B2:B1 1)),0,1)))*(B2:B11<""),0)) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. To include formula blanks "", change... (B2:B11<"") ...to... (1-ISBLANK(B2:B11)) Hope this helps! In article et, "Eric" wrote: Is there a worksheet function that can lookup the first value in a column in a list that has been filtered? In all cases the value will have been part of the filter, but done through an autofilter (not an advanced filter). Thanks, Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Issues / List Auto-Adjust | Excel Worksheet Functions | |||
Active / Dynamic Filtered List (changes w/ change in cell value) | Excel Discussion (Misc queries) | |||
Lookup with two variable data list cells | Excel Worksheet Functions | |||
Determine Frequency in Filtered List | Excel Worksheet Functions | |||
How do I paste data into filtered list in Excel? | Excel Discussion (Misc queries) |