looking up multiple continuous data??

EDITED TO INCLUDE THIS COMMENT: I completely missed the "or exceeds" comment. This formula looks for matching items.

With
A1:D100 containing this kind of data
Col_A: ref
COl_B: Field1 data...numeric
COl_C: Field1 data...numeric
COl_D: Field1 data...numeric

And F1:H1 containing numbers to be matched in the data list
example:
209
15
135

This regular formula returns the row number of the first row that contains all 3 values

Please Login or Register  to view this content.

Note: One of the last references points 1 row below the data.

See the attached file.

Is that something you can work wtih?

Seems like your data is sorted on basis of Field 1.

If E2,F2 and G2 are your input cells use

=INDEX($A$2:$D11,COUNT(A2:A11)-SUMPRODUCT(--($B$2:$B$11>$E$2),--($C$2:$C$11>$F$2),--($D$2:$D$11>$G$2))+1,2)
=INDEX($A$2:$D11,COUNT(A2:A11)-SUMPRODUCT(--($B$2:$B$11>$E$2),--($C$2:$C$11>$F$2),--($D$2:$D$11>$G$2))+1,3)
=INDEX($A$2:$D11,COUNT(A2:A11)-SUMPRODUCT(--($B$2:$B$11>$E$2),--($C$2:$C$11>$F$2),--($D$2:$D$11>$G$2))+1,4)

for desired outputs. See attached

Example: http://screencast.com/t/xQmDGp3m


This is an array formula, enter it in a cell, then confirm by pressing CTRL-SHIFT-ENTER to activate the array. You will see curly braces { } appear around your formula and the answer should appear.

=MIN(IF(($B$2:$B$11>=H2)*($C$2:$C$11>=I2)*($D$2:$D$11>=J2), A2:A11))

scratch that....by the time i pushed post a few new replys turned up, so thanks every one just need to try them out!!!!


Thanks for the quick reply Ron!!
i tried this but wasnt getting the results i was hoping for (probably because i dont understand the formula/didnt explain very well ),
although the closest to what i want so far!

the purpose of the spreadsheet is to calculate the best box size kept in stock against the input dimension.
so the table show the box number followed buy the three dimension.

again all help is great

lloydie

OK...I put together a sample file where you input desired Lenght, Width, and Height dimensions and the formulas return candidate box styles and dimensions that satisfy the requirements.

Example:
A1:D8 contains..

Please Login or Register  to view this content.

F1:H2 contains the requirements

Please Login or Register  to view this content.

Returned values from the formulas

Please Login or Register  to view this content.

Does that help?

Glad to help.