Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 133
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 150
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 422
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter the results of a list based on a previous vlookup against the same list Mizpah Excel Worksheet Functions 2 August 18th 06 10:28 AM
in data/filter/auto filter sp8 Excel Worksheet Functions 2 May 12th 06 01:03 AM
Vlookup and adv. filter HELP!!! comotoman Excel Discussion (Misc queries) 0 October 4th 05 05:05 PM
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du TC Excel Worksheet Functions 1 May 12th 05 02:06 AM
How to filter and list data based on different data. Defoes Right Boot Excel Worksheet Functions 3 April 13th 05 04:03 PM


All times are GMT +1. The time now is 09:27 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"