Thread: Looking up a maximum value in a column where the columns vary in the number of rows

First off, I'm an absolute newb to what I'm attempting to do and secondly I've tried to sift through some of the posts but just can't seem to find what I'm after. I apologize if this is covered elsewhere.

I'm trying to create a spreadsheet for dive planning. I enter a target depth in cell E17 and have the sheet round the value based on the criteria for dive planning as below:

IF(E17<=35,35,ROUNDUP(E17,-1))

So, if my target depth is under 35 feet, the value becomes 35 feet, and any intermediate value higher than 35 feet that is not a multiple of ten is rounded to the next higher value. No issue so far. Now that I have my rounded value the fun (or in my case headache) begins.

This is what my first table looks like. Depths for column headers, pressure groups for row headers. I've tried HLOOKUP and INDEX MATCH combinations. The issue is needing to get to the appropriate depth column and go down to the maximum time value and output that into a cell. Also I'd like to go across from that time value and return the lettered pressure group to another cell. Finally I'd like to be able to enter a target time and find that value or the next one higher in the depth column and return the lettered pressure group (which typically won't be the maximum value) for the target time. I'm sure if I can figure out how to get this table to work I can get the other parts of the sheet to function.

35 40 50 60 70 80 90 100 110 120 130 140
A 10 9 7 6 5 4 4 3 3 3 3 ↓
B 19 16 13 11 9 8 7 6 6 5 5 4
C 25 22 17 14 12 10 9 8 7 6 6 5
D 29 25 19 16 13 11 10 9 8 7 7 6
E 32 27 21 17 15 13 11 10 9 8 ↓ 7
F 36 31 24 19 16 14 12 11 10 9 8 8
G 40 34 26 21 18 15 13 12 11 10 9
H 44 37 28 23 19 17 15 13 12 11 10
I 48 40 31 25 21 18 16 14 13 ↓
J 52 44 33 27 22 19 17 15 ↓ 12
K 57 48 36 29 24 21 18 16 14 13
L 62 51 39 31 26 22 19 17 15
M 67 55 41 33 27 23 21 18 16
N 73 60 44 35 29 25 22 19
O 79 64 47 37 31 26 23 20
P 85 69 50 39 33 28 24
Q 92 74 53 42 35 29 25
R 100 79 57 44 36 30
S 108 85 60 47 38
T 117 91 63 49 40
U 127 97 67 52
V 139 104 71 54
W 152 111 75 55 Safety Stop
X 168 120 80 Required
Y 188 129
Z 205 140

My main issue is that most functions and examples seem to expect the same number of rows for each column but my columns are not all equal in the number of rows.

Any insight appreciated.

EDIT: I copy pasted from my table and looks fine when composing or editing, but looks jumbled when posted, sorry,
Jason

It's not clear where "the matrix" is stored... for sake of demo, let's assume:

Target depth: E17

Matrix: A20:M46

where:

A21:A46: Lettered Pressure Groups A:Z
B20:M20: Depths (35 to 140)

To generate outputs:

F17: =MATCH(IF($E$17<=35,35,MIN(ROUND($E17,-1),140)),$B$20:$M$20)
used to identify column of interest (determined by depth)

G17: =LOOKUP(9.99E+307,INDEX($B$21:$M$46,0,$F$17))
max time for depth of interest (numeric values only)

H17: =LOOKUP(9.99E+307,INDEX($B$21:$M$46,0,$F$17),$A$21:$A$46)
associated pressure group with max time for given depth

I17: Target Time for given depth (manual entry)

J17: =INDEX($A$21:$A$46,1+COUNTIF(INDEX($B$21:$M$46,0,$F$17),"<"&$I17))
associated pressure group with max time for given depth

I wasn't sure what you meant by "next time up" so I've assumed you want the first time that is >= target time

If G17 is working then there's no reason for H17 not to work... both are in effect doing the same thing only the latter has a result_vector whereas the former does not.

In both cases they simply look for the last numeric value in the column for the requisite depth and either return the value itself (G17) or the associated pressure group (H17).
(it has been assumed per your sample that the numeric values in each depth column are listed in ascending order)

If you can't get this to work please post a sample which illustrates both setup and failing formula(e) at which point we can assist further.

The "error" is the result of the "hidden" (via formatting) numeric values in row 29 of your table - remove them (L29:U29) and your output will be "Q" as expected.

If you need to retain these numbers let me know - not sure why you need them in truth given

LOOKUP(9.99E+307,J$4:J$29)

would (as we know) return the last numeric within the specified range should you require it.

On an aside, if it is your intention to store the column references in E60/E61 (makes sense) then you can obviously simplify E43:E44 such that they use those cells to est. appropriate column to use within the INDEX, eg:

E43: =LOOKUP(205,INDEX(J4:U29,0,$E$61))

On a final note - using 205 rather than 9.99E+307 doesn't really make any difference in terms of performance.
Should you modify your table such that 205 is no longer the Max value the function will cease to be watertight - 9.99E+307 is much safer
(or use MAX(range) if it makes more logical sense to you in terms of auditing).


FWIW - post your attachments here directly going forward - you can attach files via the paperclip icon in the reply window - if you can't see it click GoAdvanced

Mixing data types in the table obviously makes things more complex but assuming then you intend to keep the arrows...

Code:

E48:
=INDEX($I$4:$I$29,MATCH(SMALL(INDEX($J$4:$U$29,0,$E$61),1+COUNTIF(INDEX($J$4:$U$29,0,$E$61),"<"&$E$46)),INDEX($J$4:$U$29,0,$E$61),0))
(the same can be copied to E51 by changing ref to E61 in the above to E62)

Originally Posted by jkuetemann

The other issue that seems to be caused by the 1+COUNTIF is if a value for planned time exceeds the NDL it returns a pressure group one higher than allowed by the NDL. This is really dummy-proofing as you should never plan a dive time longer than the NDL unless you'd like to get bent

Can't you simply validate E46 against E43/E44 before conducting the remaining calcs ?
(ie you've already est. max dive times based on depth)