Return Min value in a column between range

[jlwa]

Hi.
New to this forum so please excuse, and advise of any breach of etiquette.
I have attached a spreadsheet that describes two related excel formula queries I have. Note that I have reduced the example data set to a workable size as actual spreadsheet is of a much larger and complex size.
Appreciate your help in advance.
Regards
Jack

[JesusTello]

Try this I don't know if this is that you want,

I don't get it....


Let me know

[loginjmor]

Hi -

I think you need to use the following Array Formula (CSE) to find the minimum pressure and then just INDEX/MATCH to find the corresponding depth. The two formulas look like this:

=SMALL(IF($B$4:$B$14>=$B$18,IF($B$4:$B$14<=$C$18,$D$4:$D$14,1000000000000),1000000000000),1) You need to press Ctrl-Shift-Enter simultaneously to engage the array functionality.

=INDEX($B$4:$B$14,MATCH($G$3,D4:D14,0))

Attached is your workbook with these two formulas in place.

Hope this helps.

[AlKey]

For min pressure

Please Login or Register  to view this content.

for Depth of Minimum Pressure

Please Login or Register  to view this content.

**Must be entered with Ctrl+Shift+Enter key combination.

v	B	C	D	E	F	G
1
2	TABLE 1
3	Depth	Temp	Pressure		Minimum Pressure (within interval)	1650
4	0	0	0		Depth of Minimum Pressure	300
5	100	2	550
6	200	4	1100
7	300	6	1650
8	400	8	2200
9	500	10	2750
10	600	12	3300
11	700	14	3000
12	800	16	2050
13	900	18	1650
14	1000	20	2000
15
16	Depth Interval
17	Low Bound	High Bound
18	300	800

[Bernie Deitrick]

If you change your second formula to

Please Login or Register  to view this content.

Then it will correctly find 900 when the limits are 800 to 1000.