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.

 

[SunnyAlv]

so

For this, please try this :




View attachment 102068

sorry for late information
if u want to show value in Column "A", change formula to this :

=XLOOKUP(MIN(IFERROR(ABS(C1:C20-D1:D20),"")),ABS(C1:C20-D1:D20),A1:A20)

 

[SunnyAlv]

so

sorry for late information
if u want to show value in Column "A", change formula to this :

ahh sosorry u using diferent version excel ya,
use this:

=FILTER(A1:A20,IFERROR(ABS(C1:C20-D1:D20),"")=MIN(IFERROR(ABS(C1:C20-D1:D20),"")))

 

[Fluff]

Xl 2013 does not have Xlookup or Filter.

 

[Frankroger]

ahh sosorry u using diferent version excel ya,
use this:

I don't think my excel has the filter function sorry, it says function isn't valid and won't let the formula be entered

 

[Frankroger]

Ahh I've figured how to add the index match to my formula in H1 now so all is good

Thank you for your time in helping me sir, may the gods be with you!

 

[Frankroger]

Could you quote the formula in cell H10 from this post Sunny, along with the formula you originally gave me

I can then mark it as solved in case anyone else at some point has a similar problem to this and is searching around.

You took the time to help me,
so I want to credit you with the solution Sir.
thanks for your time!

copy this formula to cell " H1 "



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

Solution -

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
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		#N/A	#N/A		#N/A
10	13/06/2017		48.40744	35.461		12.9464		14/06/2017	While Omitting #N/A cells, it takes the minimum absolute difference between columns C and D,
11	14/06/2017		48.43772	35.57819		12.8595	<<min diff		and returns its relative position in Column A, e.g 12.85 was the smallest absolute difference
12	15/06/2017		48.46799	34.28909		14.1789			so it has returned the date, 14/06/2017
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(A1:A20,MATCH((MIN(IFERROR(IF(ISERROR(ABS(C1:C20-D1:D20)),"",ABS(C1:C20-D1:D20)),ABS(C1:C20-D1:D20)))),ABS(C1:C20-D1:D20),0))
F1:F20	F1	=ABS(D1-C1)
Press CTRL+SHIFT+ENTER to enter array formulas.