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	-

 

[CA_Punit]

Two Queries
1. Can setting No be duplicates. If there can be duplicates how to deal with it. Should i show large positive value??

2. Whether the comparision is to be done on AUD/CAD--AUDCHF etc etc for each setting

 

[NeoTrader]

Two Queries
1. Can setting No be duplicates. If there can be duplicates how to deal with it. Should i show large positive value??

2. Whether the comparision is to be done on AUD/CAD--AUDCHF etc etc for each setting

Hello CA_Punit
Sorry, I am not sure what you are asking me...
I don't follow your wording

 

[CA_Punit]

1. Can setting No be duplicates. If there can be duplicates how to deal with it. Should i show large positive value??

I was enquiring can (Setting No) Stated in a Pair be repeated more than once (Eg can i have setting No 64 twice for AUDCAD pair). If yes what value to be shown. The Largest positive value??

2. Whether the comparision is to be done on AUD/CAD--AUDCHF etc etc for each setting

I meant whether the comparision of largest postive value to be done on the basis of Setting for each class (AUDCAD/AUDCNF etc etc)

 

[gaz_chops]

Try this

=IFERROR(INDEX(OFFSET($A$1,2,MATCH(B$22,$A$1:$K$1,0)-1,16,),MATCH($A24,OFFSET($A$1,2,MATCH(B$22,$A$1:$K$1,0),16,),0)),"")

 

[NeoTrader]

Hey Gaz Chops.

Thanks so much for the coding.
I copied your formula into cell as per my sheet.
See inserted formula here = ***

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
	***= formula inserted here
	1	2	3	4	5	6	7	8	9	10
	AUDCAD	AUDCHF	AUDJPY	AUDNZD	AUDUSD	CADCHF	CADJPY	CHFJPY	EURAUD	EURCAD
85	***
86
87
88
89
90
91
92
93
94
95
96
97

I copied the formula down, but no data has appeared.

I am not sure what else to do.
I am sorry to add that I am not that familiar with the functions you have used...
I look up the functions tomorrow to understand what you've produced

Any assistance is greatly appreciated

NeoTrader

 

[gaz_chops]

Maybe I misunderstood! I thought from your OP that you wanted the Output in the Table below!

My solution takes the Values for each Pair and based on their Setting, sorts them 1-100. As per the sample below.

=IFERROR(INDEX(OFFSET($A$1,2,MATCH(B$22,$A$1:$K$1,0)-1,16,),MATCH($A24,OFFSET($A$1,2,MATCH(B$22,$A$1:$K$1,0),16,),0)),"")

If this is not correct then restate what you are trying to achieve, use the XL2BB option to upload sample data, showing what results you expect.

Gaz

 

[NeoTrader]

Hey Gaz

No, no... you have understood correctly my friend....
The output is spot on

It's me, I don't get the results when I use/apply your formula into the cell... sorry about that.
The uploaded 'capture range' sent previously is correct...

I very much appreciate your assistance

If needed, using onedrive I can share a copy of the sheet with you if that makes it easier, just let me know.

my thanks

 

[gaz_chops]

OK upload and post the link here.

 

[NeoTrader]

here you go...

MrExcel FileShare

Folder

1drv.ms

That should give you access to the folder.