Vlookup with Data Filter

[Guest]

I use a Vlookup Formala for B4 The range is A10:M2000.I have a Data Filer on
colume 1.My question is is it possibl to use the data filter to select the
value and it will appear in colume B4.Thanks in advance Wayne

 

[Dave Peterson]

Not using =vlookup().

=vlookup() will find those matching cells in that lookup table no matter if any
rows are hidden.

But maybe you could "add" the same filtering rules into your formula.

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

 

[Domenic]

Assuming that A4 contains the lookup value, try the following formula
which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=VLOOKUP(A4,IF(SUBTOTAL(3,OFFSET($A$10:$A$2000,ROW($A$10:$A$2000)-ROW($A$
10),0,1)),$A$10:$M$2000),2,0)

Adjust the column index number accordingly.

Hope this helps!

 

[JMay]

If I understand what you are saying, (and I think I do)
here is a nice UDF written by Stephen Bullen that I use:
Paste this into a Standard module and in the worksheet Cell B4
just enter

=filterCriteria(YourRange)

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria
Case xlOr
Filter = Filter & " OR " & .Criteria
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function

Hope this helps,,
Jim May