Lookup Function and Match
 

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

Lookup Function and Match
 

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.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"galv2" wrote in message
...
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

Lookup Function and Match
 

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
InsertNamesCreate
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


"galv2" wrote:

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