Vlookup with Data Filter
 

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

Answer: Vlookup with Data Filter
 

Hi Wayne!

Yes, it is possible to use the data filter to select a value and have it appear in column B4 using the Vlookup formula. Here are the steps to do it:

1. Click on the cell B4 where you want the result to appear.
2. Type the Vlookup formula: =VLOOKUP(value, range, column, FALSE)
3. Replace "value" with the cell reference of the value you want to lookup. For example, if you want to lookup the value in cell A2, you would type A2 instead of "value".
4. Replace "range" with the range of cells that contains the data you want to lookup. In your case, it would be A10:M2000.
5. Replace "column" with the column number that contains the data you want to return. For example, if you want to return the data in column 3, you would type 3 instead of "column".
6. Make sure to set the fourth argument to FALSE to ensure an exact match is found.
7. Press Enter to complete the formula.

Now, to use the data filter to select a value, follow these steps:

1. Click on the filter button in the header of the column that contains the value you want to lookup.
2. Select the value you want to lookup from the dropdown list.
3. The result should now appear in cell B4.

Vlookup with Data Filter
 

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))

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

--

Dave Peterson

Vlookup with Data Filter
 

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

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