Spreadsheet: 51293d1250823937-multiple-if-functions-with-array-onlineeg
Sheet: Sheet1

This is a simplified version of a spreadsheet that I am working on. The formula for equating linear mass (B10) breaks down when the wind depth is changed to <=100m and at 410m. The equations next to table 2 are to show that the formulas work but isn't returning the value required in cell B11. please note - it is not possible to have the equations next to the tables and do vlookup as the sheet runs multiple simulations.
Factor of Safety Newly Installed | 8
Gravity | 9.81 | m/s/s
Weight of [NAME] (t) | 9.859555555555556
Wind Depth (m) | 100 | m | (maximum 1486m)
Actual Static Factor Of Safety | 8.094461681014632
Data Table | 2
Linear Mass | 0
 |  | Triangular |  |  |  |  | Locked Coil
 |  | Typical Application | [NAME], Mine Haulage |  |  |  | Typical Application | Mine Drum Winder, Friction [NAME], Aerial Track Ropes
 |  | Coating | Galvanized or Natural |  |  |  | Coating | Galvanized or Natural
 |  | [NAME] | Triangular |  |  |  | [NAME] | Locked Coil
 |  | Grade | 1770 |  |  |  | Grade | 1770
 | 1 | Nominal Diameter mm | Linear Mass kg/100m | Minimum Breaking Force kN |  | 4 | Nominal Diameter mm | Linear Mass kg/100m | Minimum Breaking Force kN
 |  | 14 | 81 | 131 |  |  | 16 | 144 | 218
 |  | 16 | 105 | 171 |  |  | 17 | 163 | 218
 |  | 18 | 133 | 216 |  |  | 18 | 183 | 276
 |  | 20 | 164 | 267 |  |  | 19 | 204 | 307
 |  | 22 | 198 | 323 |  |  | 21 | 249 | 375
 |  | 24 | 237 | 385 |  |  | 21.5 | 260 | 388
 |  | 26 | 278 | 452 |  |  | 22 | 273 | 412
 |  | 28 | 322 | 524 |  |  | 24 | 325 | 490
 |  | 32 | 421 | 685 |  |  | 26 | 381 | 575
 |  |  |  |  |  |  | 27 | 411 | 620
 | 2 | Typical Application | Mine Drum Winding, Mine Drum Haulage, Dredge Ladder, Friction Winder Head Ropes |  |  |  | 29 | 474 | 715
 |  | Coating | Galvanized or Natural |  |  |  | 30 | 508 | 765
 |  | [NAME] | Triangular |  |  |  | 31 | 542 | 775
 |  | Grade | 1770 |  |  |  | 32 | 578 | 871
 |  |  |  |  |  |  | 33 | 614 | 926
 | 2 | Nominal Diameter mm | Linear Mass kg/100m | Minimum Breaking Force kN |  |  | 35 | 691 | 1040
 |  |  |  |  |  |  | 37 | 772 | 1167
 |  | 16 | 105 | 164 | 1.677710056715807 | -7794.237845117846 | 37.5 | 793 | 1187
 |  | 18 | 132 | 206 | 2.101672809128524 | -7265.315016835018 | 38 | 814 | 1226
 |  | 20 | 164 | 255 | 2.593279764648166 | -6648.238383838384 | 40 | 902 | 1364
 |  | 22 | 200 | 312 | 3.161599055735749 | -5930.414545454546 | 40.5 | 925 | 1393
 |  | 24 | 237 | 369 | 3.725496154813447 | -5212.590707070708 | 41 | 948 | 1432
 |  | 26 | 276 | 432 | 4.34477390078226 | -4419.206464646465 | 42 | 995 | 1501
 |  | 28 | 320 | 500 | 5.006937612756235 | -3562.85521885522 | 43 | 1040 | 1570
 |  | 32 | 420 | 655 | 6.495281153450867 | -1610.878114478116 | 44 | 1090 | 1648
 |  | 36 | 530 | 825 | 8.094461681014632 | 529.9999999999993 | 45 | 1140 | 1727
 |  | 40 | 653 | 1020 | 9.89060505970386 | 2985.71313131313 | 46 | 1190 | 1805
 |  | 44 | 800 | 1250 | 11.95368777962066 | 5882.195286195282 | 47 | 1250 | 1874
 |  | 48 | 945 | 1475 | 13.91605402221606 | 8715.710437710435 | 48 | 1300 | 1962
 |  |  |  |  |  |  | 49 | 1350 | 2040
 |  | Typical Application | Mine Drum Winding, Mine Drum Haulage, Dredge Ladder, Friction Winder Head Ropes |  |  |  | 51 | 1470 | 2217
 |  | Coating | Galvanized or Natural |  |  |  | 52 | 1500 | 2286
 |  | [NAME] | Triangular |  |  |  | 53 | 1580 | 2394
 |  | Grade | 1770 |  |  |  | 54 | 1640 | 2482
 |  |  |  |  |  |  | 55 | 1700 | 2551
 | 3 | Nominal Diameter mm | Linear Mass kg/100m | Minimum Breaking Force kN |  |  | 56 | 1770 | 2668
 |  | 40 | 637 | 1020 |  |  | 57 | 1830 | 2776
 |  | 44 | 782 | 1250 |  |  | 59 | 1960 | 2923
 |  | 48 | 923 | 1475 |  |  | 60 | 2030 | 3031
 |  | 52 | 1080 | 1730 |  |  | 62 | 2170 | 3227
 |  | 56 | 1250 | 2000 |  |  | 64 | 2310 | 3443
 |  | 60 | 1440 | 2300 |  |  | 65 | 2380 | 3551