Search/Filter to find values in another range based on two cell values
 

I'm using Excel 2000 and am trying to get a search/filter that will use two cell
values as the range for the criteria. For example, if I specify a cell with the
value of 100 and the next cell in the sorted range is 120, the criteria for the
search would be all matching items that range from 100 to 119. I've tried using
advanced filter with some IF(LEFT() and IF(RIGHT) but that is too restrictive and
would require some relatively complex formulas which I'm not sure I can do. I've
also searched for examples of VBA code that simulates advanced filter but have
not found any. Below is an example of some data and the result required. Any
help will be greatly appreciated.

Cell A20 used for first part of search

A19: 90
A20: 100
A21: 120
A21: 130

Other Range Data

89
93
99
102
105
108
111
119
121
129

Items Found:
102
105
108
111
119

Search/Filter to find values in another range based on two cellvalues
 

You can use an advanced filter to extract the data.
You didn't specify how the starting value (100) is selected, so this
solution assumes you type a value onto the worksheet.

In cell G1, type 100
In cell H1, enter a formula to return the ending value:

=INDEX($A$2:$A$25,MATCH(G1,$A$2:$A$25,0)+1)

The other range data needs a heading cell (C1 in this example), and the
data starts in the row below (C2:C11)

Leave cell J1 blank, as the heading cell in the criteria range
In cell J2, enter: =AND(C2=$G$1,C2<=$H$1)

Select a cell in the list in column C
Choose DataFilterAdvanced Filter
For the Criteria Range, select J1:J2
Click OK

Or, you could add a formula beside the numbers in column C, then use an
AutoFilter to filter the list. For example, in cell D2, enter:

=AND(C2=$G$1,C2<=$H$1)
Copy the formula down to the last row of data
Add a heading in cell D1
Select cell D1, and choose DataFilterAutoFilter
From the dropdown in cell D1, choose TRUE

Andy wrote:
I'm using Excel 2000 and am trying to get a search/filter that will use two cell
values as the range for the criteria. For example, if I specify a cell with the
value of 100 and the next cell in the sorted range is 120, the criteria for the
search would be all matching items that range from 100 to 119. I've tried using
advanced filter with some IF(LEFT() and IF(RIGHT) but that is too restrictive and
would require some relatively complex formulas which I'm not sure I can do. I've
also searched for examples of VBA code that simulates advanced filter but have
not found any. Below is an example of some data and the result required. Any
help will be greatly appreciated.

Cell A20 used for first part of search

A19: 90
A20: 100
A21: 120
A21: 130

Other Range Data

89
93
99
102
105
108
111
119
121
129

Items Found:
102
105
108
111
119





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Search/Filter to find values in another range based on two cell values
 

Thank you Debra, this will work nicely. The first cell range is supplied by a
listbox control source cell (G1) and the second will be through a for each lookup
using Range("H1").Value = Range("A" & r.Row + 1).Value. The =AND is something I
hadn't come across yet in my two weeks of working with Excel 2000, but I like it
already. Thank you again.


"Debra Dalgleish" wrote in message
...
You can use an advanced filter to extract the data.
You didn't specify how the starting value (100) is selected, so this
solution assumes you type a value onto the worksheet.

In cell G1, type 100
In cell H1, enter a formula to return the ending value:

=INDEX($A$2:$A$25,MATCH(G1,$A$2:$A$25,0)+1)

The other range data needs a heading cell (C1 in this example), and the
data starts in the row below (C2:C11)

Leave cell J1 blank, as the heading cell in the criteria range
In cell J2, enter: =AND(C2=$G$1,C2<=$H$1)

Select a cell in the list in column C
Choose DataFilterAdvanced Filter
For the Criteria Range, select J1:J2
Click OK

Or, you could add a formula beside the numbers in column C, then use an
AutoFilter to filter the list. For example, in cell D2, enter:

=AND(C2=$G$1,C2<=$H$1)
Copy the formula down to the last row of data
Add a heading in cell D1
Select cell D1, and choose DataFilterAutoFilter
From the dropdown in cell D1, choose TRUE

Andy wrote:
I'm using Excel 2000 and am trying to get a search/filter that will use two
cell
values as the range for the criteria. For example, if I specify a cell with
the
value of 100 and the next cell in the sorted range is 120, the criteria for
the
search would be all matching items that range from 100 to 119. I've tried
using
advanced filter with some IF(LEFT() and IF(RIGHT) but that is too restrictive
and
would require some relatively complex formulas which I'm not sure I can do.
I've
also searched for examples of VBA code that simulates advanced filter but
have
not found any. Below is an example of some data and the result required.
Any
help will be greatly appreciated.

Cell A20 used for first part of search

A19: 90
A20: 100
A21: 120
A21: 130

Other Range Data

89
93
99
102
105
108
111
119
121
129

Items Found:
102
105
108
111
119





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html