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

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

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.