Vlookup with Data Filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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))
 
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!
 
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
 
Back
Top