Multiple Condition formula

[mecheet]

Hi All

I have an issue with the below, i have the following table where the data is sourced.

<table border="0" cellpadding="0" cellspacing="0" width="910"><col style="width: 113pt;" width="150"> <col style="width: 61pt;" width="81"> <col style="width: 68pt;" width="90"> <col style="width: 59pt;" width="78"> <col style="width: 58pt;" width="77"> <col style="width: 65pt;" width="86"> <col style="width: 70pt;" width="93"> <col style="width: 74pt;" width="98"> <col style="width: 70pt;" width="93"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 113pt;" width="150" height="17">Rates</td> <td class="xl66" style="border-left: medium none; width: 61pt;" width="81">A</td> <td class="xl66" style="border-left: medium none; width: 68pt;" width="90">B</td> <td class="xl66" style="border-left: medium none; width: 59pt;" width="78">C</td> <td class="xl66" style="border-left: medium none; width: 58pt;" width="77">D</td> <td class="xl66" style="border-left: medium none; width: 65pt;" width="86">E</td> <td class="xl66" style="border-left: medium none; width: 70pt;" width="93">F</td> <td class="xl66" style="border-left: medium none; width: 74pt;" width="98">G</td> <td class="xl66" style="border-left: medium none; width: 70pt;" width="93">H</td> <td class="xl67" style="width: 48pt;" width="64">I</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl68" style="height: 13.5pt;" height="18">Tonnage</td> <td class="xl69" style="border-left: medium none;"> </td> <td class="xl70" style="border-left: medium none;"> </td> <td class="xl69" style="border-left: medium none;"> </td> <td class="xl69" style="border-left: medium none;"> </td> <td class="xl69" style="border-left: medium none;"> </td> <td class="xl69" style="border-left: medium none;"> </td> <td class="xl69" style="border-left: medium none;"> </td> <td class="xl69" style="border-left: medium none;"> </td> <td class="xl71"> </td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl72" style="height: 20.25pt;" height="27">3.001-7.999</td> <td class="xl73" style="border-left: medium none;">10.78</td> <td class="xl73" style="border-left: medium none;">13.29</td> <td class="xl73" style="border-left: medium none;">16.32</td> <td class="xl73" style="border-left: medium none;">18.22</td> <td class="xl73" style="border-left: medium none;">21.02</td> <td class="xl73" style="border-left: medium none;">24.38</td> <td class="xl73" style="border-left: medium none;">28.21</td> <td class="xl73" style="border-left: medium none;">31.97</td> <td class="xl74">35.60</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl75" style="height: 20.25pt;" height="27">8.000-13.999</td> <td class="xl76" style="border-left: medium none;">9.53</td> <td class="xl76" style="border-left: medium none;">12.19</td> <td class="xl76" style="border-left: medium none;">15.20</td> <td class="xl76" style="border-left: medium none;">16.75</td> <td class="xl76" style="border-left: medium none;">19.89</td> <td class="xl76" style="border-left: medium none;">23.26</td> <td class="xl76" style="border-left: medium none;">27.06</td> <td class="xl76" style="border-left: medium none;">30.87</td> <td class="xl77">34.69</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl75" style="height: 20.25pt; border-top: medium none;" height="27">14,000-17.999</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">8.33</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">10.84</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">13.83</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">15.36</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">18.48</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">21.82</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">25.63</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">29.44</td> <td class="xl77" style="border-top: medium none;">33.26</td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl78" style="height: 21pt;" height="28">18,000+</td> <td class="xl79" style="border-left: medium none;">7.19</td> <td class="xl79" style="border-left: medium none;">9.32</td> <td class="xl79" style="border-left: medium none;">12.25</td> <td class="xl79" style="border-left: medium none;">13.83</td> <td class="xl79" style="border-left: medium none;">16.86</td> <td class="xl79" style="border-left: medium none;">20.12</td> <td class="xl79" style="border-left: medium none;">23.86</td> <td class="xl79" style="border-left: medium none;">27.49</td> <td class="xl80">31.13</td> </tr> </tbody></table>

I then need to do a lookup, so when rate A is selected and the tonnage for that entry is 10, that the 10 is multipied by the rate of 9.53.

This formula will need to be able to have all permitations.

Can anyone help?

Thanks

 

[MrKowz]

Try:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Rates	A	B	C	D	E	F	G	H	I
2	Tonnage
3	3	10.78	13.29	16.32	18.22	21.02	24.38	28.21	31.97	35.6
4	8	9.53	12.19	15.2	16.75	19.89	23.26	27.06	30.87	34.69
5	10	8.33	10.84	13.83	15.36	18.48	21.82	25.63	29.44	33.26
6	18	7.19	9.32	12.25	13.83	16.86	20.12	23.86	27.49	31.13
7
8	Rate	C
9	Tonnage	9
10	Value	15.2
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B10	=INDEX(B3:J6,MATCH(B9,A3:A6),MATCH(B8,B1:J1,0))