Display data value from a corresponding column

[NeoTrader]

Hi there Excel Tutors and Gurus

I hope I explain this well enough that you can help.

I have a simple data sheet produced by an MT4 trading platform (Snippet below)

The data sheet
Two columns = pair
There are 28 pairs in total (Row)
The 'setting' range is from 1 to 100 (as a column of data)
Each pair will have the number range 1 to 100
The 'setting' has produced a respective 'value' which varies (negative to positive) from pair to pair

The aim
To compare the 'settings' and corresponding 'values' of each pair 1, pair 2, pair 3 etc to ascertain which setting produces the best value (positive number)
This may be a single number or more likely a small range of 'settings' that produce the better 'value' / result

Background
I have done some draft calculations but this is hugely time consuming so I need a way to manipulate the data to either produce a simple output
I have played around with charts - that didn't go as well as desired.
I have run some basic 'IF' functions, but I am not getting the 'IF' function to perform through the whole column of data!

Solution
If there is a easy way of manipulating the data.
Once I see a formula I tend to get the workings - as a newbie, I have a lot to learn
Any guidance, assistance or answers are much appreciated.

Data Sheet

	1	AUDCAD	2	AUDCHF	3	AUDJPY	4	AUDNZD	5	AUDUSD	6
	Value	Setting	Value	Setting	Value	Setting	Value	Setting	Value	Setting	Value
	-2577.08	64	4198.95	31	6053.24	51	963.34	50	6000	45	6615.57
	-2943.27	93	3673.19	34	4656.71	49	643.56	40	5500	47	5250.61
	-3311.13	95	2098.86	35	4655.85	52	475.64	17	4500	48	5041.62
	-3311.14	89	2098.69	33	4655	55	321.78	80	4500	44	4281.88
	-3311.15	94	524.55	39	4654.99	56	318.78	53	4000	57	3992.56
	-3311.57	91	524.37	41	4190.06	54	318.78	43	4000	46	3673.32
	-3313.07	65	524.37	40	4189.6	59	222.47	15	4000	43	3231.92
	-3661.88	23	-0.71	30	3724.69	53	156.86	20	3500	53	3231.91
	-3662.53	21	-1.05	32	3723.79	60	5	90	3000	58	3231.91
	-3679.02	96	-525.41	38	3723.36	66	4	93	3000	56	2707.03
	-3679.24	87	-525.57	66	3259.75	50	3	94	3000	51	2707.03
	-3679.46	90	-525.59	42	3259.74	48	2	96	3000	49	2707.02
	-3679.65	86	-525.74	65	3258.44	57	-1	41	3000	42	2416.68
	-3680.75	73	-525.95	37	3257.99	61	-1	38	3000	39	2181.97
	-3680.75	68	-1050.48	45	3257.56	65	-3	52	2500	55	2181.96
	-4046.9	97	-1050.83	29	3257.56	64	-4	44	2000	59	2181.79

Data output idea ?

	1	2	3	4	5	6	7
	AUDCAD	AUDCHF	AUDJPY	AUDNZD	AUDUSD	CADCHF	CADJPY
1	-
2	-
3	-
4	-
5	-
6	-
7	-
8	-
9	-
10	-
11	-
12	-
13	-
14	-

 

[gaz_chops]

OK I have amended the formula to match your data ranges

=IFERROR(INDEX(OFFSET($A$1,2,MATCH(B$111,$A$1:$BD$1,0)-1,100,),MATCH($A113,OFFSET($A$1,2,MATCH(B$111,$A$1:$BD$1,0),100,),0)),"")

I can't sign in to test it properly, enter the formula in Cell B113, let me know if it works.

 

[NeoTrader]

oh my.

Entered the formula... nothing appears

Odd you say you cant sign in to test it properly... I didnt place a password on the folder... it should be available via the link with permission to edit also - I'll re-check the folder and and file in the shared drive!

 

[NeoTrader]

Just reset the file access... please try again

value and setting copy for mrexcel V1.0.xlsx

1drv.ms

 

[gaz_chops]

No it's my login that isn't working!

I found the problem, the numbers under Setting columns are not numbers! They are text!

Easiest way to fix is to change the Numbers in Col A in the Output Table to be Text, that way it will match, as below.

Enter 1 as '1 then 2 as '2 then highlight '1 & '2 and drag down, all the data should appear.

 

[NeoTrader]

Righto Gaz...

Column A113:A215 now as text with numbers 1 to 103... done
re-applied formula to cell B113 and scrolled down to B215. done

the output column is still blank...
what am i missing here :l

can you place your formula into the shared file in onedrive ? is that possible to solve this oddity ?

 

[gaz_chops]

I just changed the open file, can you see the changes?

 

[NeoTrader]

Marvellous Work Gaz...
you star

I have copied your formula from the shared file and loaded into the master sheet with success...
(Odd that i F2 the cell B113 and the formula and selected cells are the same as before - but its working

Now I need to figure out how to best use the data produced in the table...

Thank you so much for your sterling work
?

 

[gaz_chops]

You're welcome mate, if you want a quick explanation of the formula let me know.