INDEX Formula help

[ddub25]

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

=IF(AND(B77="Alias",C57>0),(C77/F57),IF(B77="Investor","N/A",0))

It should,
-Look in Column A on the lower table and realise that the Month/Year on the same row is Oct-10.
-Look for a MATCH of Oct-10 in Column A on the 1st table. (It should find a match in row 57)
-IF MATCH found AND IF B77 = "Alias" AND IF C77 >0
-----Calculate: C77/F57 (£135.98/£193.64) [these are the two corresponding rows to the Date matches found]
-----IF NOT return 0
-----In all cases, IF B77 = "Investor" return "N/A"

<table width="629" 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"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" 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> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">56</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Sep-10</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;"> £ 416.91 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 230.02 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 230.02 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 37.35 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">57</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;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,894.28 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 753.34 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 753.34 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 193.64 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">58</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;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,382.71 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 2,001.68 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 2,001.68 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 115.20 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">59</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;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 2,127.31 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 3,590.05 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 3,590.05 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 245.43 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">60</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;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,152.42 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,090.97 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,087.75 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 149.38 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">61</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;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,770.75 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 3,287.68 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 2,249.29 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 147.80 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">62</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;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,261.16 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 773.33 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 593.49 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 164.63 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">63</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;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,566.67 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 2,248.77 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,618.81 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 150.52 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">64</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;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,455.26 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,829.41 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,454.32 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 178.91 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">65</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;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 2,324.57 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 5,025.41 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 3,982.14 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 468.83 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">66</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;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,693.51 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,056.87 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 1,297.45 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 435.35 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">67</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;"> </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="xl70" 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"> </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;"> £ 17,045.55 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 21,887.53 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> £ 18,858.32 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> £ 2,287.04 </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20"> </td> <td class="xl66" style="border-left: medium none;">A</td> <td class="xl66" style="border-left: medium none;">B</td> <td class="xl66" style="border-left: medium none;">C</td> <td class="xl66" style="border-left: medium none;">D</td> <td class="xl66" style="border-left: medium none;">E</td> <td class="xl66" style="border-left: medium none;">F</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">76</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Sep-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;"> £ - </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> </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;">0.00%</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">70.22%</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">40.58%</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">35.58%</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> </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> </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> </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">53.92%</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">32.31%</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">64.66%</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">57.30%</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;">#DIV/0!</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">0.00%</td> </tr> <tr style="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> </tr> </tbody></table>

Can Anyone help? Thanks.

Dan

 

[BeingYogi]

May Be:


=IF(B77="Investor","NA",IF(C77=0,0,(C77*(INDEX($F$56:$F$67,MATCH(A77,$A$56:$A$67,0),0)))))

 

[taurean]

This should work:
==IF(B77="Investor","N/A",IF(AND(B77="Alias",C77<>"-"),IF(INDEX($A$56:$F$67,MATCH($A77,$A$56:$A$67,0),3)>0,($C77/INDEX($A$56:$F$67,MATCH($A77,$A$56:$A$67,0),6)),0),0))

 

[ddub25]

Thanks Taurean, yours did exactly what I was after. I also tried to incorporate:

IF B96="Owner", return "N/A" (as well as IF B96="Investor", return "N/A")

but trying to incorporate an IF(OR caused syntax errors. Any ideas how to incorporate that to:

=IF(B77="Investor","N/A",IF(AND(B77="Alias",C77<>"-"),IF(INDEX($A$56:$F$67,MATCH($A77,$A$56:$A$67,0),3)>0,($C77/INDEX($A$56:$F$67,MATCH($A77,$A$56:$A$67,0),6)),0),0))

Dan

 

[taurean]

Glad to help you. This should work:
=IF(OR(B77="Investor",B77="Owner"),"N/A",IF(AND(B77="Alias",C77<>"-"),IF(INDEX($A$56:$F$67,MATCH($A77,$A$56:$A$67,0),3)>0,($C77/INDEX($A$56:$F$67,MATCH($A77,$A$56:$A$67,0),6)),0),0))
But if "Alias is the case where formula needs to calculate (and rest is N/A) then the formula can be simplified as below:
=IF(B77<>"Alias","N/A",IF(C77<>"-",IF(INDEX($A$56:$F$67,MATCH($A77,$A$56:$A$67,0),3)>0,($C77/INDEX($A$56:$F$67,MATCH($A77,$A$56:$A$67,0),6)),0),0))

 

[ddub25]

Thanks taurean, the last formula suggestion is exactly what I wanted and makes it simpler. Thanks again.

Dan