lookup value froma filtered list

[Eric]

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

 

[Guest]

The function VLOOKUP should be able to do that whether the list / value is
filtered or not.

 

[Guest]

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

 

[Domenic]

Assuming that B1 contains your header, and B2:B11 contains your data,
try...

=INDEX(B2:B11,MATCH(1,(SUBTOTAL(3,OFFSET(B2:B11,ROW(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!

 

[Eric]

Wow - that's some formula! I'll try it after I get over my headache from
looking at it.

Thanks!