One possibility is illustrated in this sample construct:
http://www.savefile.com/files/601484
Display lines satisfying multicriteria tolerances.xls
Assume source table in sheet: X, cols A to D, data from row1 down
Cols A and B are the key cols housing numeric values
In another sheet: Y,
Base inputs for X's cols A and B will be made in A2:A3
Put in B2: =A2*0.9
Put in C2: =A2*1.1
Select B2:C2, copy down to C3. B2:C3 computes the lower and upper tolerance
limits (+/- 10%) for the 2 base inputs.
Put in E1:
=IF(OR($A$2="",$A$3=""),"",IF(AND(X!A1=$B$2,X!A1< =$C$2,X!B1=$B$3,X!B1<=$C$3),ROW(),""))
Put in F1:
=IF(ROW()COUNT($E:$E),"",INDEX(X!A:A,SMALL($E:$E, ROW())))
Copy F1 to I1. Select E1:I1, copy down to cover the max expected extent of
data in X, say down to I100. Hide away col E. Cols F to I will return the
required results, ie only those lines from X satisfying the input criteria's
tolerances for col A and col B, with all results neatly bunched at the top.
Adapt and extend to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Andy K" wrote:
From a table of information I would like to display a full row or rows of
data based on a set of lookup criteria, if a1:a20 has a value = to 20
+ or- 10% and the same row has a value of 50 + or - 10% b1:b20
The values will always change, therefore I would like the formula to look at
a cell where I can input the required value and automatically filter when
the value is changed
To help you understand the spreadsheet is a simple list of equipment model
numbers with capacities fan dimensions and noise data I would like to input 3
Selection criteria and be presented with each row that complies within 10%
Thanks for any help you can provide