INDEX Formula Help

[ddub25]

I have the formula below which is located in H77 (returning 0.43%) in the lower table and which manages to return the results I want,
but I'd like it to be more sophisticated.

=IF(C77=0,0,IF(SUMIFS($E$32:$E$43,$A$32:$A$43,$A$71,$B$32:$B$43,"Investor")=0,G77*F77,SUMIFS($E$32:$E$43,$A$32:$A$43,$A$71,$B$32:$B$43,"Investor")))

It should,
-Look in A71 and MATCH that text with text in the upper table in Range A32:A43 [It would find a match in A32]
-IF the corresponding text in Column B (B32) = "Owner" OR = "Investor" then return the value in the corresponding cell in column E (E32)
-IF the corresponding text in Column B (B32) DOES NOT= "Owner" OR = "Investor" then calculate G77*F77.
-IF NOT, return 0

<table width="903" border="0" cellpadding="0" cellspacing="0"><col style="width: 37pt;" width="49"> <col style="width: 61pt;" width="81"> <col style="width: 58pt;" width="77"> <col style="width: 78pt;" width="104"> <col style="width: 82pt;" width="109"> <col style="width: 86pt;" width="114"> <col style="width: 71pt;" width="95"> <col style="width: 63pt;" width="84"> <col style="width: 68pt;" width="90"> <col style="width: 75pt;" width="100"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 37pt;" width="49" height="20"> </td> <td class="xl73" style="border-left: medium none; width: 61pt;" width="81">A</td> <td class="xl73" style="border-left: medium none; width: 58pt;" width="77">B</td> <td class="xl73" style="border-left: medium none; width: 78pt;" width="104">C</td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="109">D</td> <td class="xl73" style="border-left: medium none; width: 86pt;" width="114">E</td> <td class="xl73" style="border-left: medium none; width: 71pt;" width="95">F</td> <td class="xl73" style="border-left: medium none; width: 63pt;" width="84">G</td> <td class="xl73" style="border-left: medium none; width: 68pt;" width="90">H</td> <td class="xl73" style="border-left: medium none; width: 75pt;" width="100">I</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">32</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Andy M</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.36%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 961.63 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">7.14%</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 339.06 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">33</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Chris W</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Investor</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">3.75%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,263.02 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,456.92 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">34</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Dan W</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Owner</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">87.50%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,587.90 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 2,038.28 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">35</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Dunc M</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">-£ 31.46 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2.79 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.49%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,325.41 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">9.84%</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,855.66 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">36</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Emily H</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 68.10 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2.79 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.57%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,524.68 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">11.32%</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 3,383.22 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">37</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Jack J</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 32.24 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2.79 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.53%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,420.91 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">10.55%</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,289.96 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">38</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Jill W</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Investor</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">3.75%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 769.57 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 437.48 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">39</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Rich W</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">-£ 31.01 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2.79 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.61%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,648.84 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">12.24%</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 3,035.96 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">40</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Sal W</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 50.23 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2.79 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.67%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,808.43 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">13.42%</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,754.19 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">41</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Stew F</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">-£ 61.46 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2.79 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.57%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,536.55 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">11.40%</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,545.66 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">42</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Tim W</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">-£ 4.35 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2.79 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1.20%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 3,246.76 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">24.10%</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 4,751.13 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">43</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Yun Z</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ - </td> </tr> </tbody></table>
<table width="903" border="0" cellpadding="0" cellspacing="0"><col style="width: 37pt;" width="49"> <col style="width: 61pt;" width="81"> <col style="width: 58pt;" width="77"> <col style="width: 78pt;" width="104"> <col style="width: 82pt;" width="109"> <col style="width: 86pt;" width="114"> <col style="width: 71pt;" width="95"> <col style="width: 63pt;" width="84"> <col style="width: 68pt;" width="90"> <col style="width: 75pt;" width="100"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 37pt;" width="49" height="20"> </td> <td class="xl66" style="border-left: medium none; width: 61pt;" width="81">A</td> <td class="xl66" style="border-left: medium none; width: 58pt;" width="77">B</td> <td class="xl66" style="border-left: medium none; width: 78pt;" width="104">C</td> <td class="xl66" style="border-left: medium none; width: 82pt;" width="109">D</td> <td class="xl66" style="border-left: medium none; width: 86pt;" width="114">E</td> <td class="xl66" style="border-left: medium none; width: 71pt;" width="95">F</td> <td class="xl66" style="border-left: medium none; width: 63pt;" width="84">G</td> <td class="xl66" style="border-left: medium none; width: 68pt;" width="90">H</td> <td class="xl66" style="border-left: medium none; width: 75pt;" width="100">I</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">71</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Andy M</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl84" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl84" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">72</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl84" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl84" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">73</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl84" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl84" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">74</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl84" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl84" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">75</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">76</td> <td class="xl79" style="border-left: medium none;">Sep-10</td> <td class="xl80" style="border-left: medium none;">Alias</td> <td class="xl81" style="border-left: medium none;"> £ - </td> <td class="xl81" style="border-left: medium none;"> £ - </td> <td class="xl82" style="border-left: medium none;">0.00%</td> <td class="xl83" style="border-left: medium none;" align="right">0.00%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl74" style="border-left: medium none;">0.00%</td> <td class="xl75" style="border-left: medium none;"> £ - </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">77</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Oct-10</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 135.98 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 3.39 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">7.18%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">8.69%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.43%</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> £ 3.27 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">78</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Nov-10</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 46.75 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 161.50 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">3.38%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">3.88%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.19%</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> £ 3.88 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">79</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Dec-10</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 87.33 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 179.50 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">4.11%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">5.20%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.26%</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> £ 9.34 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">80</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Jan-11</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">0.00%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> £ - </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">81</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Feb-11</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">0.00%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> £ - </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">82</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Mar-11</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">0.00%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> £ - </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">83</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Apr-11</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 81.16 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 61.50 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">5.18%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">7.56%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.38%</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> £ 6.12 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">84</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">May-11</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 57.80 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">-£ 84.50 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">3.97%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">5.33%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.27%</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> £ 3.87 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">85</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Jun-11</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 303.16 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">-£ 195.78 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">13.04%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">16.96%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.85%</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> £ 33.76 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">86</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Jul-11</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 249.45 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 213.45 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">14.73%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">19.37%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.97%</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> £ 12.57 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">87</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Aug-11</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">0.00%</td> <td class="xl74" style="border-left: medium none;">5.00%</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> £ - </td> </tr> <tr style="page-break-before: always; height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 961.63 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 339.06 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> £ 72.81 </td> </tr> </tbody></table>

Can Anyone help? Thanks.

Dan

 

[njimack]

Try this:
=IF(C77=0,0,IF(OR(INDEX($B$32:$B$43,MATCH($A$71,$A$32:$A$43,0))={"Owner","Investor"}),E77,G77*F77))

 

[cgcamal]

Hi ddub,

Similarly, another option.

Excel Workbook
	H
77	0.004345
...

Cell Formulas
Range	Formula
H77	=IF(C77=0,0,IF(OR(LOOKUP(A71,A32:B43)={"Owner","Investor"}),INDEX(E32:E43,MATCH(A71,A32:A43,0)),G77*F77))

Regards

 

[ddub25]

Sorry, I probably wasn't very clear. If "Owner" or "Investor" are found in column B on the same row as
the matched name, then return the value in Column E of the same row.

In the current situation the formula finds a match with "Andy M" (from A71) in A32 and the
same row in column B contains "Alias" and so the (G77*F77) calculation is made. However, let's say, if
B32 = "Owner" OR "Investor" I would want the value in E32 to be returned (0.36%). At present it would return
7.18% (E77). Any ideas?

Dan

 

[cgcamal]

Hi Dan,

Dis you try exactly with the last formula?, it seems that returns exactly desired result:

Excel Workbook
	A	B	C	D	E	F	G	H	I
32	Andy M	Owner			0.36%	961.63	7.14%		339.06
33	Chris W	Investor			3.75%	1,263.02			1,456.92
34	Dan W	Owner			87.50%	1,587.90			2,038.28
35	Dunc M	Alias	- 31.46	2.79	0.49%	1,325.41	9.84%		1,855.66
36	Emily H	Alias	68.10	2.79	0.57%	1,524.68	11.32%		3,383.22
37	Jack J	Alias	32.24	2.79	0.53%	1,420.91	10.55%		1,289.96
38	Jill W	Investor			3.75%	769.57			437.48
39	Rich W	Alias	- 31.01	2.79	0.61%	1,648.84	12.24%		3,035.96
40	Sal W	Alias	50.23	2.79	0.67%	1,808.43	13.42%		1,754.19
41	Stew F	Alias	- 61.46	2.79	0.57%	1,536.55	11.40%		1,545.66
42	Tim W	Alias	- 4.35	2.79	1.20%	3,246.76	24.10%		4,751.13
43	Yun Z				0.00%	-	0.00%		-
44
70
71	Andy M
76
77	oct-10	Alias	135.98	3.39	7.18%	8.69%	5.00%	0.36%
...

Cell Formulas
Range	Formula
H77	=IF(C77=0,0,IF(OR(LOOKUP(A71,A32:B43)={"Owner","Investor"}),INDEX(E32:E43,MATCH(A71,A32:A43,0)),G77*F77))

Hope this helps.

Regards.

 

[ddub25]

Thanks cgcamal, thats what I was after. Thanks very much for your time - you are a great help.

Dan

 

[cgcamal]

You're welcome Dan, nice to know it helped you.

Regards.

 

[ddub25]

CGCAMAL, how did you create your table?

 

[cgcamal]

Dan, you can use MrExcel HTML Maker to post your samples in the forum. Check it out.

Hope it helps.

Regards.