Lookup Function and Match

[Guest]

Hey
Im a final yr degree student .
Loading (KN)
4kN 5kN 6kN 7kN
Depth
100 2.5 2.3 2.4 2.5
150 2.9 2.5 2.7 2.8
200 2.6 2.7 2.4 2.2
250 2.7 2.7 2.7 2.7

The Left Hand Column contains the Depth of the Slab Required in mm
The Top Row is The MAX Loading Applied in Kn
The internal values are the distance the slab must span in metres

My problem is that if I were to pick a load value of 6kn over a slab
distance of 2.6m then i would like it to return the corresponding slab depth;
in this case 150mm or 250mm is sufficent. Similarily if i was to choose a
load of 7Kn and a slab span of 2.8m then it would return a depth value of
150mm

 

[Ragdyer]

With your posted example in A1 to E5, and your slab distance entered in F1,
and your Kn load entered in F2, try this *array* formula:

=INDEX(A1:A5,MAX(IF((A2:E5>=F1)*(A1:E1=F2),ROW(2:5))))

*OR*

=INDEX(A1:A5,MAX(IF((A2:E5<=F1)*(A1:E1=F2),ROW(2:5))))

Which ever one comes closer to your specs.

 

[Guest]

Try this:

First, create this table in Cells A1:E5

Depth 4kN 5kN 6kN 7kn
100 2.5 2.3 2.4 2.5
150 2.9 2.5 2.7 2.8
200 2.6 2.7 2.4 2.2
250 2.7 2.7 2.7 2.7


Select that range
Insert>Names>Create
Check:Top Row and Click OK

You'll now have these range names: Depth, _4Kn, _5Kn, _6Kn, _7Kn

F1: 2.6
G1: 6Kn
H1: 1
H2: 2
H3: 3
H4: 4

I1: =LARGE(--(--((INDIRECT("_"&$G$1)>=$F$1)*(ROW(Depth)-1))>0)*Depth,H1)
Note: Commit that array formula by Holding down [Ctrl][Shift] when you press
enter

Copy that formula down to I4

Changing combination of distance and load should return valid depths.
2.6 and 6kn returns: 250 and 150
2.8 and 7Kn returns: 150
2.5 and 7Kn returns: 250, 150, and 100

Does that do what you want?

***********
Regards,
Ron

XL2002, WinXP-Pro