I need help with look up a function

[SMISTRETTA]

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:

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

<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>
</tbody>

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

 

[Simon4s]

=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

 

[SMISTRETTA]

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

 

[SMISTRETTA]

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

 

[Simon4s]

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

still "-"?

 

[SMISTRETTA]

I'm afraid so...

 

[Simon4s]

I'm afraid so...

Why not just use large then?
Large(x,1)
Large(x,2)... etc

I think the small is counting the %s that do not have headings? Not sure

 

[SMISTRETTA]

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.

 

[Simon4s]

You do not have headings for all your columns?