Smallest difference between columns B and C, returning the position of column A, ommiting #N/A and blanks

[Frankroger]

I'm trying to get the position of column A, by finding the absolute minimum difference between column ranges C and D

Formula in cell H3, works, but then breaks down when error cells are included in the ranges

In this example, the smallest absolute difference between ranges C1:C6 and D1:D6 was cells C3 and D3, as the difference between these two cells was the smallest.
The formula then returned to me cell A3, value 3, which is correct.

But if we include #N/A cells in the ranges, it fails.

The formula in H1, omits the errors, and returns to me the smallest absolute difference between columns C and D, but I can't get it to return the position from column A

So either I need to be able to add an index match function to the formula in H1, to return the position from column A

Or I need to be able to add the ignore errors function into the formula in H3

I've been unable to solve

help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	1		10	20		10		2	The formula omits #N/A error cells and finds the smallest absolute difference between columns C and D, which was 2, but I need it to return the position from cell A3
2	2		10	20		10
3	3		7	9		2
4	4		10	20		10		3	This formula does what I'm trying to do! , It find the smallest difference between colums C and D and returns the position from Column A, but it doesn't omit #N/A cells!
5	5		10	20		10
6	6		10	20		10
7
8
9
10	1		10	20		10		#N/A	#N/A values in ranges breaks the formula
11	2		10	20		10
12	3		7	9		2
13	4		10	20		10
14	5		#N/A	20		#N/A
15	6		10	20		10
Sheet1

Cell Formulas
Range	Formula
H1	H1	=MIN(IFERROR(IF(ISERROR(ABS(C1:C6-D1:D6)),"",ABS(C1:C6-D1:D6)),ABS(C1:C6-D1:D6)))
H4	H4	=INDEX(A1:A6,MATCH(MIN(ABS(C1:C6-D1:D6)),ABS(C1:C6-D1:D6),0))
F10:F15,F1:F6	F1	=ABS(D1-C1)
H10	H10	=INDEX(A10:A15,MATCH(MIN(ABS(C10:C15-D10:D15)),ABS(C10:C15-D10:D15),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Frankroger]

Any help would be greatly appreciated, this problem has bugged me for days, I'm so close but no cigar =(

 

[SunnyAlv]

copy this formula to cell " H1 "

=INDEX($A$1:$A$6,MATCH(MIN(IFERROR(IF(ISERROR(ABS(C1:C6-D1:D6)),TRUE,ABS(C1:C6-D1:D6)),ABS(C1:C6-D1:D6))),ABS(C1:C6-D1:D6)))

its helping u to know ABS from "C1:C6-D1:D6", although have #N/A condition and the formula will showing Column A based on ABS"C1:C6-D1:D6"

*
*If there’s any unclear information please let me know

 

[Frankroger]

It worked on my example set!!

But for some bizarre reason not on my actual data set

Thanks so much helping me!

help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	04/06/2017		48.13497	#N/A		#N/A
2	05/06/2017		48.16525	#N/A		#N/A
3	06/06/2017		48.19552	#N/A		#N/A
4	07/06/2017		48.2258	#N/A		#N/A
5	08/06/2017		48.25607	#N/A		#N/A
6	09/06/2017		48.28635	#N/A		#N/A
7	10/06/2017		48.31662	#N/A		#N/A
8	11/06/2017		48.34689	#N/A		#N/A
9	12/06/2017		48.37717	#N/A		#N/A
10	13/06/2017		48.40744	#N/A		#N/A		#N/A	#N/A values in ranges breaks the formula
11	14/06/2017		48.43772	#N/A		#N/A			Should Return cell A640
12	15/06/2017		48.46799	64		15.532
13	16/06/2017		48.49826	63		14.5017
14	17/06/2017		48.52854	62		13.4715
15	18/06/2017		48.55881	50		1.44119
16	19/06/2017		48.58909	49		0.41091
17	20/06/2017		48.61936	#N/A		#N/A
18	21/06/2017		48.64964	#N/A		#N/A
19	22/06/2017		48.67991	#N/A		#N/A
20	23/06/2017		48.71018	#N/A		#N/A
Sheet1

Cell Formulas
Range	Formula
H10	H10	=INDEX($A$1:$A$20,MATCH(MIN(IFERROR(IF(ISERROR(ABS(C1:C20-D1:D20)),TRUE,ABS(C1:C20-D1:D20)),ABS(C1:C20-D1:D20))),ABS(C1:C20-D1:D20)))
F1:F20	F1	=ABS(D1-C1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Frankroger]

Cell should return A16 in the above example sorry, not A649.

I can't seem to edit my posts to correct mistake ahh

 

[Frankroger]

Columns C and D can both potentially have #N/A values in their ranges sorry

 

[Frankroger]

I'm not sure why the formula won't work on this range set

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	04/06/2017		48.13497	33		15.135
2	05/06/2017		48.16525	33.11719		15.0481
3	06/06/2017		48.19552	33.23438		14.9611
4	07/06/2017		48.2258	34.40628		13.8195
5	08/06/2017		48.25607	34.52347		13.7326
6	09/06/2017		48.28635	34.64067		13.6457
7	10/06/2017		48.31662	35.10943		13.2072
8	11/06/2017		48.34689	35.22662		13.1203
9	12/06/2017		48.37717	35.34381		13.0334
10	13/06/2017		48.40744	35.461		12.9464		#N/A	#N/A values in ranges breaks the formula
11	14/06/2017		48.43772	35.57819		12.8595	<<min diff		Should return Cell A11
12	15/06/2017		48.46799	34.28909		14.1789
13	16/06/2017		48.49826	34.40628		14.092
14	17/06/2017		48.52854	34.52347		14.0051
15	18/06/2017		48.55881	34.64067		13.9181
16	19/06/2017		#N/A	#N/A		#N/A
17	20/06/2017		48.61936	35.22662		13.3927
18	21/06/2017		48.64964	35.34381		13.3058
19	22/06/2017		48.67991	35.461		13.2189
20	23/06/2017		48.71018	35.57819		13.132
Sheet1

Cell Formulas
Range	Formula
H10	H10	=INDEX($A$1:$A$20,MATCH(MIN(IFERROR(IF(ISERROR(ABS(C1:C20-D1:D20)),TRUE,ABS(C1:C20-D1:D20)),ABS(C1:C20-D1:D20))),ABS(C1:C20-D1:D20)))
F1:F20	F1	=ABS(D1-C1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Frankroger]

It seems to be the highlighted cells that are causing the problem, as though it's getting confused when minimum difference between columns C and D are quite close together

help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
9	20/01/2016		113.255	33.544		79.711		22/01/2016	Formula Works
10	21/01/2016		113.263	33.0855		80.1771
11	22/01/2016		113.325	34.555		78.7703
12	23/01/2016		113.388	#N/A		#N/A
13	24/01/2016		#N/A	#N/A		#N/A
14	25/01/2016		113.513	33.0732		80.4399
15	26/01/2016		#N/A	#N/A		#N/A
16	27/01/2016		121.15	34.541		86.609
17	28/01/2016		122.155	#N/A		#N/A
18	29/01/2016		123.223	#N/A		#N/A
19	30/01/2016		#N/A	#N/A		#N/A
20	31/01/2016		#N/A	#N/A		#N/A
21	01/02/2016		#N/A	#N/A		#N/A
22	02/02/2016		#N/A	#N/A		#N/A
23	03/02/2016		#N/A	#N/A		#N/A
24	04/02/2016		#N/A	#N/A		#N/A
25
26
27
28	20/01/2016		48.135	33		15.135		#N/A	Formula doesn鈥檛 work?
29	21/01/2016		48.165	33.117		15.048
30	22/01/2016		48.196	33.234		14.962
31	23/01/2016		48.226	34.406		13.82
32	24/01/2016		48.256	34.523		13.733
33	25/01/2016		48.286	34.641		13.645
34	26/01/2016		48.317	35.109		13.208
35	27/01/2016		48.347	35.227		13.12
36	28/01/2016		48.377	35.344		13.033
37	29/01/2016		48.407	35.461		12.946
38	30/01/2016		48.438	35.578		12.86
39	31/01/2016		#N/A	#N/A		#N/A
40	01/02/2016		48.498	34.406		14.092
41	02/02/2016		48.529	34.523		14.006
42	03/02/2016		48.559	34.641		13.918
43	04/02/2016		#N/A	#N/A		#N/A
44
45
46
47	20/01/2016		48.135	33		15.135		27/01/2016	If we remove the 3 highlighted cells, it works again?
48	21/01/2016		48.165	33.117		15.048
49	22/01/2016		48.196	33.234		14.962
50	23/01/2016		48.226	34.406		13.82
51	24/01/2016		48.256	34.523		13.733
52	25/01/2016		48.286	34.641		13.645
53	26/01/2016		48.317	35.109		13.208
54	27/01/2016		48.347	35.227		13.12
55	28/01/2016		48.377			48.377
56	29/01/2016		48.407			48.407
57	30/01/2016		48.438			48.438
58	31/01/2016		#N/A	#N/A		#N/A
59	01/02/2016		48.498	34.406		14.092
60	02/02/2016		48.529	34.523		14.006
61	03/02/2016		48.559	34.641		13.918
62	04/02/2016		#N/A	#N/A		#N/A
Sheet2

Cell Formulas
Range	Formula
H9,H28	H9	=INDEX($A$9:$A$24,MATCH(MIN(IFERROR(IF(ISERROR(ABS(C9:C24-D9:D24)),TRUE,ABS(C9:C24-D9:D24)),ABS(C9:C24-D9:D24))),ABS(C9:C24-D9:D24)))
A53:A62,A34:A43,A15:A24	A15	=A14+1
F47:F62,F28:F43,F9:F24	F9	=ABS(D9-C9)
H47	H47	=INDEX($A$47:$A$62,MATCH(MIN(IFERROR(IF(ISERROR(ABS(C47:C62-D47:D62)),TRUE,ABS(C47:C62-D47:D62)),ABS(C47:C62-D47:D62))),ABS(C47:C62-D47:D62)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[SunnyAlv]

It worked on my example set!!

But for some bizarre reason not on my actual data set

Thanks so much helping me!

help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	04/06/2017		48.13497	#N/A		#N/A
2	05/06/2017		48.16525	#N/A		#N/A
3	06/06/2017		48.19552	#N/A		#N/A
4	07/06/2017		48.2258	#N/A		#N/A
5	08/06/2017		48.25607	#N/A		#N/A
6	09/06/2017		48.28635	#N/A		#N/A
7	10/06/2017		48.31662	#N/A		#N/A
8	11/06/2017		48.34689	#N/A		#N/A
9	12/06/2017		48.37717	#N/A		#N/A
10	13/06/2017		48.40744	#N/A		#N/A		#N/A	#N/A values in ranges breaks the formula
11	14/06/2017		48.43772	#N/A		#N/A			Should Return cell A640
12	15/06/2017		48.46799	64		15.532
13	16/06/2017		48.49826	63		14.5017
14	17/06/2017		48.52854	62		13.4715
15	18/06/2017		48.55881	50		1.44119
16	19/06/2017		48.58909	49		0.41091
17	20/06/2017		48.61936	#N/A		#N/A
18	21/06/2017		48.64964	#N/A		#N/A
19	22/06/2017		48.67991	#N/A		#N/A
20	23/06/2017		48.71018	#N/A		#N/A
Sheet1

Cell Formulas
Range	Formula
H10	H10	=INDEX($A$1:$A$20,MATCH(MIN(IFERROR(IF(ISERROR(ABS(C1:C20-D1:D20)),TRUE,ABS(C1:C20-D1:D20)),ABS(C1:C20-D1:D20))),ABS(C1:C20-D1:D20)))
F1:F20	F1	=ABS(D1-C1)
Press CTRL+SHIFT+ENTER to enter array formulas.

For this, please try this :

=AGGREGATE(15,6,ABS(C1:C20-D1:D20),ROW(A1))

 

[SunnyAlv]

I'm not sure why the formula won't work on this range set

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	04/06/2017		48.13497	33		15.135
2	05/06/2017		48.16525	33.11719		15.0481
3	06/06/2017		48.19552	33.23438		14.9611
4	07/06/2017		48.2258	34.40628		13.8195
5	08/06/2017		48.25607	34.52347		13.7326
6	09/06/2017		48.28635	34.64067		13.6457
7	10/06/2017		48.31662	35.10943		13.2072
8	11/06/2017		48.34689	35.22662		13.1203
9	12/06/2017		48.37717	35.34381		13.0334
10	13/06/2017		48.40744	35.461		12.9464		#N/A	#N/A values in ranges breaks the formula
11	14/06/2017		48.43772	35.57819		12.8595	<<min diff		Should return Cell A11
12	15/06/2017		48.46799	34.28909		14.1789
13	16/06/2017		48.49826	34.40628		14.092
14	17/06/2017		48.52854	34.52347		14.0051
15	18/06/2017		48.55881	34.64067		13.9181
16	19/06/2017		#N/A	#N/A		#N/A
17	20/06/2017		48.61936	35.22662		13.3927
18	21/06/2017		48.64964	35.34381		13.3058
19	22/06/2017		48.67991	35.461		13.2189
20	23/06/2017		48.71018	35.57819		13.132
Sheet1

Cell Formulas
Range	Formula
H10	H10	=INDEX($A$1:$A$20,MATCH(MIN(IFERROR(IF(ISERROR(ABS(C1:C20-D1:D20)),TRUE,ABS(C1:C20-D1:D20)),ABS(C1:C20-D1:D20))),ABS(C1:C20-D1:D20)))
F1:F20	F1	=ABS(D1-C1)
Press CTRL+SHIFT+ENTER to enter array formulas.

try this

=AGGREGATE(15,6,ABS(C1:C20-D1:D20),ROW(A1))