I need help with look up a function

S

SMISTRETTA

New Member
Joined
Jul 23, 2010
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Hi there, can someone please be so kind as to help me resolve this issue. I am doing a study for my research article.

I have the spreadsheet below:

[TABLE="width: 3132"]
<colgroup><col><col><col><col><col><col><col span="2"><col><col span="2"><col><col span="2"><col><col span="2"><col span="3"><col span="7"><col span="2"><col><col span="2"><col><col span="2"><col><col span="9"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD] S&P [/TD]
[TD][/TD]
[TD][/TD]
[TD] FTSE [/TD]
[TD][/TD]
[TD][/TD]
[TD] MSCI W [/TD]
[TD][/TD]
[TD][/TD]
[TD] STOXX [/TD]
[TD][/TD]
[TD][/TD]
[TD] HSI [/TD]
[TD][/TD]
[TD][/TD]
[TD]NDX[/TD]
[TD][/TD]
[TD][/TD]
[TD]FTSE250[/TD]
[TD][/TD]
[TD][/TD]
[TD]NIKKEI[/TD]
[TD][/TD]
[TD][/TD]
[TD]DAX[/TD]
[TD][/TD]
[TD][/TD]
[TD]<st1:stockticker>CAC</st1:stockticker>[/TD]
[TD][/TD]
[TD][/TD]
[TD]MIB[/TD]
[TD][/TD]
[TD][/TD]
[TD]IBEX[/TD]
[TD][/TD]
[TD][/TD]
[TD]VIX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Months [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD] [/TD]
[TD]Ave >0[/TD]
[TD]Ave <0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Jan [/TD]
[TD]3.12%[/TD]
[TD]-5.01%[/TD]
[TD] Jan [/TD]
[TD]3.42%[/TD]
[TD]-3.99%[/TD]
[TD] Jan [/TD]
[TD]3.50%[/TD]
[TD]-5.39%[/TD]
[TD] Jan [/TD]
[TD]3.95%[/TD]
[TD]-7.95%[/TD]
[TD] Jan [/TD]
[TD]4.59%[/TD]
[TD]-9.21%[/TD]
[TD] Jan [/TD]
[TD]3.98%[/TD]
[TD]-4.94%[/TD]
[TD] Jan [/TD]
[TD]4.39%[/TD]
[TD]-2.14%[/TD]
[TD] Jan [/TD]
[TD]2.81%[/TD]
[TD]-8.18%[/TD]
[TD] Jan [/TD]
[TD]5.15%[/TD]
[TD]-8.33%[/TD]
[TD] Jan [/TD]
[TD]4.35%[/TD]
[TD]-7.22%[/TD]
[TD] Jan [/TD]
[TD]5.15%[/TD]
[TD]-8.29%[/TD]
[TD] Jan [/TD]
[TD]3.26%[/TD]
[TD]-7.49%[/TD]
[TD] Jan [/TD]
[TD]14.69%[/TD]
[TD]-15.85%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Feb [/TD]
[TD]2.87%[/TD]
[TD]-5.55%[/TD]
[TD] Feb [/TD]
[TD]2.29%[/TD]
[TD]-4.11%[/TD]
[TD] Feb [/TD]
[TD]3.17%[/TD]
[TD]-2.79%[/TD]
[TD] Feb [/TD]
[TD]3.76%[/TD]
[TD]-3.99%[/TD]
[TD] Feb [/TD]
[TD]3.13%[/TD]
[TD]-2.31%[/TD]
[TD] Feb [/TD]
[TD]4.34%[/TD]
[TD]-3.04%[/TD]
[TD] Feb [/TD]
[TD]3.63%[/TD]
[TD]-1.69%[/TD]
[TD] Feb [/TD]
[TD]4.12%[/TD]
[TD]-2.30%[/TD]
[TD] Feb [/TD]
[TD]4.01%[/TD]
[TD]-2.92%[/TD]
[TD] Feb [/TD]
[TD]4.10%[/TD]
[TD]-2.69%[/TD]
[TD] Feb [/TD]
[TD]3.96%[/TD]
[TD]-6.32%[/TD]
[TD] Feb [/TD]
[TD]3.47%[/TD]
[TD]-3.34%[/TD]
[TD] Feb [/TD]
[TD]15.32%[/TD]
[TD]-14.69%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Mar [/TD]
[TD]3.42%[/TD]
[TD]-1.09%[/TD]
[TD] Mar [/TD]
[TD]2.92%[/TD]
[TD]-2.23%[/TD]
[TD] Mar [/TD]
[TD]2.85%[/TD]
[TD]-1.57%[/TD]
[TD] Mar [/TD]
[TD]3.29%[/TD]
[TD]-1.56%[/TD]
[TD] Mar [/TD]
[TD]2.18%[/TD]
[TD]-3.82%[/TD]
[TD] Mar [/TD]
[TD]4.44%[/TD]
[TD]-1.88%[/TD]
[TD] Mar [/TD]
[TD]4.38%[/TD]
[TD]-1.25%[/TD]
[TD] Mar [/TD]
[TD]5.85%[/TD]
[TD]-3.72%[/TD]
[TD] Mar [/TD]
[TD]4.17%[/TD]
[TD]-1.94%[/TD]
[TD] Mar [/TD]
[TD]2.92%[/TD]
[TD]-1.47%[/TD]
[TD] Mar [/TD]
[TD]3.68%[/TD]
[TD]-3.77%[/TD]
[TD] Mar [/TD]
[TD]2.50%[/TD]
[TD]-3.28%[/TD]
[TD] Mar [/TD]
[TD]15.34%[/TD]
[TD]-7.67%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Apr [/TD]
[TD]3.03%[/TD]
[TD]-1.38%[/TD]
[TD] Apr [/TD]
[TD]3.32%[/TD]
[TD]-1.55%[/TD]
[TD] Apr [/TD]
[TD]4.26%[/TD]
[TD]-1.28%[/TD]
[TD] Apr [/TD]
[TD]5.52%[/TD]
[TD]-3.50%[/TD]
[TD] Apr [/TD]
[TD]6.26%[/TD]
[TD]-0.35%[/TD]
[TD] Apr [/TD]
[TD]4.99%[/TD]
[TD]-1.47%[/TD]
[TD] Apr [/TD]
[TD]3.70%[/TD]
[TD]-3.16%[/TD]
[TD] Apr [/TD]
[TD]5.75%[/TD]
[TD]-3.19%[/TD]
[TD] Apr [/TD]
[TD]5.66%[/TD]
[TD]-2.75%[/TD]
[TD] Apr [/TD]
[TD]4.75%[/TD]
[TD]-3.64%[/TD]
[TD] Apr [/TD]
[TD]7.64%[/TD]
[TD]-3.90%[/TD]
[TD] Apr [/TD]
[TD]5.04%[/TD]
[TD]-4.34%[/TD]
[TD] Apr [/TD]
[TD]10.68%[/TD]
[TD]-10.68%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] May [/TD]
[TD]2.55%[/TD]
[TD]-4.67%[/TD]
[TD] May [/TD]
[TD]2.30%[/TD]
[TD]-4.14%[/TD]
[TD] May [/TD]
[TD]3.35%[/TD]
[TD]-4.49%[/TD]
[TD] May [/TD]
[TD]2.90%[/TD]
[TD]-4.70%[/TD]
[TD] May [/TD]
[TD]7.64%[/TD]
[TD]-4.01%[/TD]
[TD] May [/TD]
[TD]4.35%[/TD]
[TD]-5.79%[/TD]
[TD] May [/TD]
[TD]2.31%[/TD]
[TD]-5.29%[/TD]
[TD] May [/TD]
[TD]4.03%[/TD]
[TD]-6.53%[/TD]
[TD] May [/TD]
[TD]4.63%[/TD]
[TD]-3.74%[/TD]
[TD] May [/TD]
[TD]2.49%[/TD]
[TD]-4.48%[/TD]
[TD] May [/TD]
[TD]2.75%[/TD]
[TD]-4.98%[/TD]
[TD] May [/TD]
[TD]4.72%[/TD]
[TD]-5.19%[/TD]
[TD] May [/TD]
[TD]30.58%[/TD]
[TD]-14.28%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Jun [/TD]
[TD]1.41%[/TD]
[TD]-3.03%[/TD]
[TD] Jun [/TD]
[TD]3.21%[/TD]
[TD]-3.84%[/TD]
[TD] Jun [/TD]
[TD]2.44%[/TD]
[TD]-2.54%[/TD]
[TD] Jun [/TD]
[TD]3.54%[/TD]
[TD]-3.29%[/TD]
[TD] Jun [/TD]
[TD]2.62%[/TD]
[TD]-6.68%[/TD]
[TD] Jun [/TD]
[TD]2.46%[/TD]
[TD]-3.73%[/TD]
[TD] Jun [/TD]
[TD]2.82%[/TD]
[TD]-3.60%[/TD]
[TD] Jun [/TD]
[TD]2.76%[/TD]
[TD]-3.06%[/TD]
[TD] Jun [/TD]
[TD]1.59%[/TD]
[TD]-3.71%[/TD]
[TD] Jun [/TD]
[TD]3.11%[/TD]
[TD]-3.85%[/TD]
[TD] Jun [/TD]
[TD]6.39%[/TD]
[TD]-4.61%[/TD]
[TD] Jun [/TD]
[TD]5.45%[/TD]
[TD]-4.52%[/TD]
[TD] Jun [/TD]
[TD]13.04%[/TD]
[TD]-16.94%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Jul [/TD]
[TD]4.10%[/TD]
[TD]-1.96%[/TD]
[TD] Jul [/TD]
[TD]4.67%[/TD]
[TD]-2.49%[/TD]
[TD] Jul [/TD]
[TD]4.47%[/TD]
[TD]-1.85%[/TD]
[TD] Jul [/TD]
[TD]4.52%[/TD]
[TD]-4.54%[/TD]
[TD] Jul [/TD]
[TD]4.88%[/TD]
[TD]0.00%[/TD]
[TD] Jul [/TD]
[TD]4.23%[/TD]
[TD]-2.14%[/TD]
[TD] Jul [/TD]
[TD]5.40%[/TD]
[TD]-2.03%[/TD]
[TD] Jul [/TD]
[TD]2.31%[/TD]
[TD]-1.90%[/TD]
[TD] Jul [/TD]
[TD]5.16%[/TD]
[TD]-3.15%[/TD]
[TD] Jul [/TD]
[TD]5.14%[/TD]
[TD]-4.44%[/TD]
[TD] Jul [/TD]
[TD]5.91%[/TD]
[TD]-4.46%[/TD]
[TD] Jul [/TD]
[TD]7.73%[/TD]
[TD]-3.22%[/TD]
[TD] Jul [/TD]
[TD]33.88%[/TD]
[TD]-12.39%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Aug [/TD]
[TD]2.29%[/TD]
[TD]-3.67%[/TD]
[TD] Aug [/TD]
[TD]2.73%[/TD]
[TD]-2.45%[/TD]
[TD] Aug [/TD]
[TD]2.05%[/TD]
[TD]-3.26%[/TD]
[TD] Aug [/TD]
[TD]3.78%[/TD]
[TD]-3.71%[/TD]
[TD] Aug [/TD]
[TD]2.03%[/TD]
[TD]-3.40%[/TD]
[TD] Aug [/TD]
[TD]3.33%[/TD]
[TD]-3.08%[/TD]
[TD] Aug [/TD]
[TD]4.27%[/TD]
[TD]-3.01%[/TD]
[TD] Aug [/TD]
[TD]2.93%[/TD]
[TD]-4.32%[/TD]
[TD] Aug [/TD]
[TD]1.98%[/TD]
[TD]-5.39%[/TD]
[TD] Aug [/TD]
[TD]3.73%[/TD]
[TD]-3.93%[/TD]
[TD] Aug [/TD]
[TD]4.83%[/TD]
[TD]-4.58%[/TD]
[TD] Aug [/TD]
[TD]4.45%[/TD]
[TD]-3.14%[/TD]
[TD] Aug [/TD]
[TD]14.36%[/TD]
[TD]-13.05%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Sep [/TD]
[TD]3.49%[/TD]
[TD]-5.94%[/TD]
[TD] Sep [/TD]
[TD]2.72%[/TD]
[TD]-6.95%[/TD]
[TD] Sep [/TD]
[TD]4.40%[/TD]
[TD]-8.58%[/TD]
[TD] Sep [/TD]
[TD]3.29%[/TD]
[TD]-7.52%[/TD]
[TD] Sep [/TD]
[TD]6.40%[/TD]
[TD]-12.30%[/TD]
[TD] Sep [/TD]
[TD]5.09%[/TD]
[TD]-6.73%[/TD]
[TD] Sep [/TD]
[TD]3.73%[/TD]
[TD]-7.06%[/TD]
[TD] Sep [/TD]
[TD]5.00%[/TD]
[TD]-5.06%[/TD]
[TD] Sep [/TD]
[TD]3.56%[/TD]
[TD]-7.05%[/TD]
[TD] Sep [/TD]
[TD]3.37%[/TD]
[TD]-6.73%[/TD]
[TD] Sep [/TD]
[TD]3.40%[/TD]
[TD]-4.19%[/TD]
[TD] Sep [/TD]
[TD]4.68%[/TD]
[TD]-4.06%[/TD]
[TD] Sep [/TD]
[TD]54.25%[/TD]
[TD]-7.72%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Oct [/TD]
[TD]4.31%[/TD]
[TD]-5.67%[/TD]
[TD] Oct [/TD]
[TD]3.67%[/TD]
[TD]-4.13%[/TD]
[TD] Oct [/TD]
[TD]4.37%[/TD]
[TD]-6.26%[/TD]
[TD] Oct [/TD]
[TD]4.36%[/TD]
[TD]-6.46%[/TD]
[TD] Oct [/TD]
[TD]6.25%[/TD]
[TD]-14.61%[/TD]
[TD] Oct [/TD]
[TD]6.02%[/TD]
[TD]-6.53%[/TD]
[TD] Oct [/TD]
[TD]3.64%[/TD]
[TD]-8.73%[/TD]
[TD] Oct [/TD]
[TD]1.47%[/TD]
[TD]-5.55%[/TD]
[TD] Oct [/TD]
[TD]4.96%[/TD]
[TD]-5.72%[/TD]
[TD] Oct [/TD]
[TD]3.69%[/TD]
[TD]-6.54%[/TD]
[TD] Oct [/TD]
[TD]5.15%[/TD]
[TD]-8.34%[/TD]
[TD] Oct [/TD]
[TD]5.43%[/TD]
[TD]-6.53%[/TD]
[TD] Oct [/TD]
[TD]24.32%[/TD]
[TD]-15.86%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Nov [/TD]
[TD]2.74%[/TD]
[TD]-3.16%[/TD]
[TD] Nov [/TD]
[TD]2.26%[/TD]
[TD]-2.02%[/TD]
[TD] Nov [/TD]
[TD]2.31%[/TD]
[TD]-4.24%[/TD]
[TD] Nov [/TD]
[TD]2.73%[/TD]
[TD]-3.58%[/TD]
[TD] Nov [/TD]
[TD]2.46%[/TD]
[TD]-3.82%[/TD]
[TD] Nov [/TD]
[TD]4.00%[/TD]
[TD]-5.24%[/TD]
[TD] Nov [/TD]
[TD]2.87%[/TD]
[TD]-2.94%[/TD]
[TD] Nov [/TD]
[TD]7.75%[/TD]
[TD]-4.17%[/TD]
[TD] Nov [/TD]
[TD]3.48%[/TD]
[TD]-3.03%[/TD]
[TD] Nov [/TD]
[TD]3.10%[/TD]
[TD]-3.09%[/TD]
[TD] Nov [/TD]
[TD]2.17%[/TD]
[TD]-4.70%[/TD]
[TD] Nov [/TD]
[TD]1.46%[/TD]
[TD]-4.74%[/TD]
[TD] Nov [/TD]
[TD]17.23%[/TD]
[TD]-9.76%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Dec [/TD]
[TD]2.04%[/TD]
[TD]-0.46%[/TD]
[TD] Dec [/TD]
[TD]2.72%[/TD]
[TD]-2.33%[/TD]
[TD] Dec [/TD]
[TD]2.99%[/TD]
[TD]-1.18%[/TD]
[TD] Dec [/TD]
[TD]2.80%[/TD]
[TD]-1.91%[/TD]
[TD] Dec [/TD]
[TD]2.43%[/TD]
[TD]-1.83%[/TD]
[TD] Dec [/TD]
[TD]3.79%[/TD]
[TD]-1.25%[/TD]
[TD] Dec [/TD]
[TD]4.42%[/TD]
[TD]-1.45%[/TD]
[TD] Dec [/TD]
[TD]6.04%[/TD]
[TD]-1.22%[/TD]
[TD] Dec [/TD]
[TD]3.48%[/TD]
[TD]-2.44%[/TD]
[TD] Dec [/TD]
[TD]3.16%[/TD]
[TD]-1.68%[/TD]
[TD] Dec [/TD]
[TD]4.44%[/TD]
[TD]-2.03%[/TD]
[TD] Dec [/TD]
[TD]2.63%[/TD]
[TD]-4.11%[/TD]
[TD] Dec [/TD]
[TD]12.75%[/TD]
[TD]-16.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Best/Worst [/TD]
[TD]4.31%[/TD]
[TD]-5.94%[/TD]
[TD] [/TD]
[TD]4.67%[/TD]
[TD]-6.95%[/TD]
[TD] [/TD]
[TD]4.47%[/TD]
[TD]-8.58%[/TD]
[TD] [/TD]
[TD]5.52%[/TD]
[TD]-7.95%[/TD]
[TD] [/TD]
[TD]7.64%[/TD]
[TD]-14.61%[/TD]
[TD] [/TD]
[TD]6.02%[/TD]
[TD]-6.73%[/TD]
[TD] [/TD]
[TD]5.40%[/TD]
[TD]-8.73%[/TD]
[TD] [/TD]
[TD]7.75%[/TD]
[TD]-8.18%[/TD]
[TD] [/TD]
[TD]5.66%[/TD]
[TD]-8.33%[/TD]
[TD] [/TD]
[TD]5.14%[/TD]
[TD]-7.22%[/TD]
[TD] [/TD]
[TD]7.64%[/TD]
[TD]-8.34%[/TD]
[TD] [/TD]
[TD]7.73%[/TD]
[TD]-7.49%[/TD]
[TD] [/TD]
[TD]54.25%[/TD]
[TD]-16.94%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] 1st [/TD]
[TD][/TD]
[TD][/TD]
[TD] 2nd [/TD]
[TD][/TD]
[TD][/TD]
[TD] 3rd [/TD]
[TD][/TD]
[TD][/TD]
[TD] 4th [/TD]
[TD][/TD]
[TD][/TD]
[TD] 5th [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 1st [/TD]
[TD][/TD]
[TD][/TD]
[TD] 2nd [/TD]
[TD][/TD]
[TD][/TD]
[TD] 3rd [/TD]
[TD][/TD]
[TD][/TD]
[TD] 4th [/TD]
[TD][/TD]
[TD][/TD]
[TD] 5th [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Months [/TD]
[TD] Buy [/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD] Buy [/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD] Buy [/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD] Buy [/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD] Buy [/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD] [/TD]
[TD] Sell [/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD]Sell[/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD]Sell[/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD]Sell[/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD]Sell[/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD]Sell[/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD]Sell[/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD]Sell[/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[TD]Sell[/TD]
[TD]Perf[/TD]
[TD]Prob[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Jan [/TD]
[TD]x (VIX)[/TD]
[TD]14.69%[/TD]
[TD][/TD]
[TD] y (DAX)[/TD]
[TD]5.15%[/TD]
[TD][/TD]
[TD] y (MIB)[/TD]
[TD]5.15%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.59%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.39%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-15.85%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-9.21%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-8.33%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-8.29%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-8.18%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-7.95%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-7.49%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-7.22%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-5.39%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Feb [/TD]
[TD]x [/TD]
[TD]15.32%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.34%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.12%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.10%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.01%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-14.69%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-6.32%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-5.55%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.11%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.99%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.34%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.04%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-2.92%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-2.79%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Mar [/TD]
[TD]x[/TD]
[TD]15.34%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.85%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.44%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.38%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.17%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-7.67%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.82%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.77%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.72%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.28%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-2.23%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-1.94%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-1.88%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-1.57%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Apr [/TD]
[TD] [/TD]
[TD]10.68%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]7.64%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]6.26%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.75%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.66%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-10.68%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.34%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.90%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.64%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.50%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.19%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.16%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-2.75%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-1.55%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] May [/TD]
[TD] [/TD]
[TD]30.58%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]7.64%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.72%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.63%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.35%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-14.28%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-6.53%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-5.79%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-5.29%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-5.19%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.98%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.70%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.67%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.49%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Jun [/TD]
[TD] [/TD]
[TD]13.04%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]6.39%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.45%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]3.54%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]3.21%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-16.94%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-6.68%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.61%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.52%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.85%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.84%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.73%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.71%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.60%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Jul [/TD]
[TD] [/TD]
[TD]33.88%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]7.73%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.91%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.40%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.16%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-12.39%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.54%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.46%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.44%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.22%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.15%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-2.49%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-2.14%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-2.03%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Aug [/TD]
[TD] [/TD]
[TD]14.36%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.83%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.45%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.27%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]3.78%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-13.05%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-5.39%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.58%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.32%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.93%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.71%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.67%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.40%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.26%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Sep [/TD]
[TD] [/TD]
[TD]54.25%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]6.40%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.09%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.00%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.68%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-12.30%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-8.58%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-7.72%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-7.52%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-7.06%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-7.05%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-6.95%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-6.73%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-6.73%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Oct [/TD]
[TD] [/TD]
[TD]24.32%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]6.25%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]6.02%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.43%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.15%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-15.86%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-14.61%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-8.73%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-8.34%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-6.54%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-6.53%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-6.53%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-6.46%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-6.26%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Nov [/TD]
[TD] [/TD]
[TD]17.23%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]7.75%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.00%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]3.48%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]3.10%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-9.76%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-5.24%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.74%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.70%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.24%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.17%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.82%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.58%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-3.16%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Dec [/TD]
[TD] [/TD]
[TD]12.75%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]6.04%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.44%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]4.42%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]3.79%[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-16.25%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-4.11%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-2.44%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-2.33%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-2.03%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-1.91%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-1.83%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-1.68%[/TD]
[TD][/TD]
[TD] [/TD]
[TD]-1.45%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
What i would like to achieve is the following, where you see the red x, i would like a formula that looks at the results in blue and looks for that result in the table array above and then returns the answer - which should be whatever the heading is in green.

If that is not clear, lets look at y in red above. The answer here is DAX and/or MIB. The formula must look up 5.15% in the table above and then find that it is in the DAX AND MIB columns. The problem here is that there are two 5.15% values. So ideally in the 2nd column it will display DAX and in the 3rd column it will display MIB.

So the correct answers are displayed in the below table in italics.

I hope this makes sense and I would greatly appreciate it if someone will be able to help me with this formula.

Many thanks
Steve
 
Sort by date Sort by votes
=index($B1:$AN1,match(large($b3:$AN3,1),$b3:$AN3,0))

Returns the text with the 1st largest value

B1:AN1 is your green texts: S&P,FTSE,etc
B3:AN3 is your January row

Edit: i dont know what to do when value are the same
 
Last edited:
Upvote 0
That is truly brilliant. Thank you so much Simon, i've spent the entire afternoon trying to get the index & match function to work for me. I am very grateful, thanks again
 
Upvote 0
Hi Simon

Sorry to be a pain but can I just confirm that for the negative numbers I use =INDEX($C$183:$AN$183,MATCH(SMALL($C185:$AN185,1),$C185:$AN185,0)). The result is displaying "-" which seems odd as all i did was replace "large" with "small" and the order, ie. 1 for the first smallest number.

Thank you
Steve
 
Upvote 0
what happens if you do SMALL($C185:$AN185,2) or SMALL($C185:$AN185,10)

still "-"?
 
Upvote 0
It's weird. I go up to Largex,13) but it doesn't seem to pull in negative numbers, which is very odd. I can pull in everything that is positive.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
163
AhoyNC
A
A
  • Solved
Select current month in combobox on userform for English language
2 3
Replies
29
Views
649
Ali M
A
G
  • Question Question
Copy some specific columns whose header is merge
2 3
Replies
26
Views
738
Joe4
Joe4
J
  • Question Question
auto filling cells based on preset format
Replies
1
Views
77
HighAndWilder
H
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
1K
Cubist
Cubist

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top