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

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

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

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

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