Vlookup with Data Filter
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
"Domenic" wrote in message
:
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!
In article ,
Wayne wrote:
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
|