finding data with multiple criteria
 

I am familiar with the VLOOPKUP function but cannot come up with a way
to pull the data I need as there are too many variables.

I need to search column 1 for the initial set of criteria,
then column 2 but ONLY within the rows that match column 1 criteria.
(And the criteria is /= .066 or </= .065)
Then search column 3 but ONLY within the rows that matched column 1 &
column 2 criteria. (rounding up to the next highest column)

now it gets trickier...
I need to search in Column Headings of columns 4 thru 9, find the
correct data and go down to the row that matched the first set of
criteria.
AND the column headings are 4.8, 6.4 & 7.9 but the number I'm searching
on might be 5.9 so I need to round up to 6.4!

The data I am searching for is
OP1 .105 5 5.9
the yield I'm looking for matches Line 2

OP1 (so now I am searching only in Lines 1 thru 3)
.066 (.105 is greaster than or equal to .066,
so now I am searching in Lines 2 & 3 only.)
5 (5 is greater than or equal to 3 but less than the next row 7,
so now I am searching only in Line 2)
6.4 (5.9 is greater than 4.8 and less than or equal to 6.4)
the answer is 22.

SAMPLE TABLE
Name Range 1 Range 2 4.8 6.4 7.9
OP 1 0.065 1 23.5 30.3 35
OP 1 0.066 3 17 22 23
OP 1 0.066 7 10 12 16
OP 2 0.065 1 15 26.3 33.4
OP 2 0.066 3 26.7 28.2 34.8
OP 2 0.066 7 20.4 22.6 27.3
OP 3 0.065 1 6.4 7.5 9.5
OP 3 0.066 3 5.2 8.4 7.2
OP 3 0.066 7 3.8 4.2 5.4


Perhaps this is too much to ask on my first post!
TIA


--
carstowal
------------------------------------------------------------------------
carstowal's Profile: http://www.excelforum.com/member.php...o&userid=37476
View this thread: http://www.excelforum.com/showthread...hreadid=571375

finding data with multiple criteria
 

Consider using Autofilter.


You can filter on any number of separate variables (columns). A neat trick
to use if you have multiple criteria on the same column is to replicate the
column and set filters for each criteria on each of the replicated columns.
--
Gary''s Student


"carstowal" wrote:


I am familiar with the VLOOPKUP function but cannot come up with a way
to pull the data I need as there are too many variables.

I need to search column 1 for the initial set of criteria,
then column 2 but ONLY within the rows that match column 1 criteria.
(And the criteria is /= .066 or </= .065)
Then search column 3 but ONLY within the rows that matched column 1 &
column 2 criteria. (rounding up to the next highest column)

now it gets trickier...
I need to search in Column Headings of columns 4 thru 9, find the
correct data and go down to the row that matched the first set of
criteria.
AND the column headings are 4.8, 6.4 & 7.9 but the number I'm searching
on might be 5.9 so I need to round up to 6.4!

The data I am searching for is
OP1 .105 5 5.9
the yield I'm looking for matches Line 2

OP1 (so now I am searching only in Lines 1 thru 3)
.066 (.105 is greaster than or equal to .066,
so now I am searching in Lines 2 & 3 only.)
5 (5 is greater than or equal to 3 but less than the next row 7,
so now I am searching only in Line 2)
6.4 (5.9 is greater than 4.8 and less than or equal to 6.4)
the answer is 22.

SAMPLE TABLE
Name Range 1 Range 2 4.8 6.4 7.9
OP 1 0.065 1 23.5 30.3 35
OP 1 0.066 3 17 22 23
OP 1 0.066 7 10 12 16
OP 2 0.065 1 15 26.3 33.4
OP 2 0.066 3 26.7 28.2 34.8
OP 2 0.066 7 20.4 22.6 27.3
OP 3 0.065 1 6.4 7.5 9.5
OP 3 0.066 3 5.2 8.4 7.2
OP 3 0.066 7 3.8 4.2 5.4


Perhaps this is too much to ask on my first post!
TIA


--
carstowal
------------------------------------------------------------------------
carstowal's Profile: http://www.excelforum.com/member.php...o&userid=37476
View this thread: http://www.excelforum.com/showthread...hreadid=571375

finding data with multiple criteria
 

I don't want to just view a list and I don't want to have to filter
every time I need an answer.

I have over a thousand cells to fill in
For example to fill in column Z, I need to seach in the table for the
data in constant cell "$Z$1" for OP1, OP2, etc.
then narrow down the search by the data in column G
then narrow down the search by the data in coumn K

then in the single line of data I am down to, find the size in the
column that corresponds to column K in my original spreadsheet.


--
carstowal
------------------------------------------------------------------------
carstowal's Profile: http://www.excelforum.com/member.php...o&userid=37476
View this thread: http://www.excelforum.com/showthread...hreadid=571375