Conditional maximum and minimum values

I have an array of "x" and "y" data which gets updated frequently.

I need to calculate the maximum value of "x" that has a "y" value < a value give in a specific reference cell outside the array.

I also need to calculate the minimum value of "x" that has a "y" value >= that same reference value.

To make it a little less abstract, here is a hypothetical example. I have set of data with x=height and y=weight. I have a reference cell into which I type a weight; say 175 pounds in this example. So I need to write formulas that find, in the x-y array, the tallest person less than 175 pounds and the shortest person >=175 pounds.

So another way to look at it might be to say, "Find the maximum value of "x" looking only at data pairs that have "y" < 175, find the minimum value of "x" looking only at data pairs that have "y" >= 175

Often when I am really stuck with tough formula coding, I will write some intermediate cells with formulae that get me to an answer when I can't figure out how to do it in one cell (by this I mean one cell for each condition). That type of solution would be acceptable here.

Thanks for any help and/or suggestions,
metsci

See if you can adapt these array formula

Cell F1 is used as the "weight" inlut cell

{=MAX(IF(B2:B100<F1,A2:A100))}

{=MIN(IF(B2:B100>=F1,A2:A100))}

Array formulas are committed using key combination Ctrl + Shift + Enter.
Adjust range references as needed.

Click Edit Post below your post, then click Go Advanced and scroll down to Manage Attachments.