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

Frankroger

New Member
Joined
Nov 16, 2023
Messages
21
Office Version
  1. 2013
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
111020102The 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
22102010
33792
441020103This 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!
55102010
66102010
7
8
9
101102010#N/A#N/A values in ranges breaks the formula
112102010
123792
134102010
145#N/A20#N/A
156102010
Sheet1
Cell Formulas
RangeFormula
H1H1=MIN(IFERROR(IF(ISERROR(ABS(C1:C6-D1:D6)),"",ABS(C1:C6-D1:D6)),ABS(C1:C6-D1:D6)))
H4H4=INDEX(A1:A6,MATCH(MIN(ABS(C1:C6-D1:D6)),ABS(C1:C6-D1:D6),0))
F10:F15,F1:F6F1=ABS(D1-C1)
H10H10=INDEX(A10:A15,MATCH(MIN(ABS(C10:C15-D10:D15)),ABS(C10:C15-D10:D15),0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Any help would be greatly appreciated, this problem has bugged me for days, I'm so close but no cigar =(
 
Upvote 0
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 :)
 
Upvote 0
It worked on my example set!!

But for some bizarre reason not on my actual data set 🤷‍♂️

Thanks so much helping me! 👍

help.xlsx
ABCDEFGHIJKL
104/06/201748.13497#N/A#N/A
205/06/201748.16525#N/A#N/A
306/06/201748.19552#N/A#N/A
407/06/201748.2258#N/A#N/A
508/06/201748.25607#N/A#N/A
609/06/201748.28635#N/A#N/A
710/06/201748.31662#N/A#N/A
811/06/201748.34689#N/A#N/A
912/06/201748.37717#N/A#N/A
1013/06/201748.40744#N/A#N/A#N/A#N/A values in ranges breaks the formula
1114/06/201748.43772#N/A#N/AShould Return cell A640
1215/06/201748.467996415.532
1316/06/201748.498266314.5017
1417/06/201748.528546213.4715
1518/06/201748.55881501.44119
1619/06/201748.58909490.41091
1720/06/201748.61936#N/A#N/A
1821/06/201748.64964#N/A#N/A
1922/06/201748.67991#N/A#N/A
2023/06/201748.71018#N/A#N/A
Sheet1
Cell Formulas
RangeFormula
H10H10=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:F20F1=ABS(D1-C1)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Cell should return A16 in the above example sorry, not A649.

I can't seem to edit my posts to correct mistake ahh
 
Upvote 0
Columns C and D can both potentially have #N/A values in their ranges sorry
 
Upvote 0
I'm not sure why the formula won't work on this range set

Book2
ABCDEFGHIJKLM
104/06/201748.134973315.135
205/06/201748.1652533.1171915.0481
306/06/201748.1955233.2343814.9611
407/06/201748.225834.4062813.8195
508/06/201748.2560734.5234713.7326
609/06/201748.2863534.6406713.6457
710/06/201748.3166235.1094313.2072
811/06/201748.3468935.2266213.1203
912/06/201748.3771735.3438113.0334
1013/06/201748.4074435.46112.9464#N/A#N/A values in ranges breaks the formula
1114/06/201748.4377235.5781912.8595<<min diffShould return Cell A11
1215/06/201748.4679934.2890914.1789
1316/06/201748.4982634.4062814.092
1417/06/201748.5285434.5234714.0051
1518/06/201748.5588134.6406713.9181
1619/06/2017#N/A#N/A#N/A
1720/06/201748.6193635.2266213.3927
1821/06/201748.6496435.3438113.3058
1922/06/201748.6799135.46113.2189
2023/06/201748.7101835.5781913.132
Sheet1
Cell Formulas
RangeFormula
H10H10=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:F20F1=ABS(D1-C1)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
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
ABCDEFGHIJKLMNO
920/01/2016113.25533.54479.71122/01/2016Formula Works
1021/01/2016113.26333.085580.1771
1122/01/2016113.32534.55578.7703
1223/01/2016113.388#N/A#N/A
1324/01/2016#N/A#N/A#N/A
1425/01/2016113.51333.073280.4399
1526/01/2016#N/A#N/A#N/A
1627/01/2016121.1534.54186.609
1728/01/2016122.155#N/A#N/A
1829/01/2016123.223#N/A#N/A
1930/01/2016#N/A#N/A#N/A
2031/01/2016#N/A#N/A#N/A
2101/02/2016#N/A#N/A#N/A
2202/02/2016#N/A#N/A#N/A
2303/02/2016#N/A#N/A#N/A
2404/02/2016#N/A#N/A#N/A
25
26
27
2820/01/201648.1353315.135#N/AFormula doesn鈥檛 work?
2921/01/201648.16533.11715.048
3022/01/201648.19633.23414.962
3123/01/201648.22634.40613.82
3224/01/201648.25634.52313.733
3325/01/201648.28634.64113.645
3426/01/201648.31735.10913.208
3527/01/201648.34735.22713.12
3628/01/201648.37735.34413.033
3729/01/201648.40735.46112.946
3830/01/201648.43835.57812.86
3931/01/2016#N/A#N/A#N/A
4001/02/201648.49834.40614.092
4102/02/201648.52934.52314.006
4203/02/201648.55934.64113.918
4304/02/2016#N/A#N/A#N/A
44
45
46
4720/01/201648.1353315.13527/01/2016If we remove the 3 highlighted cells, it works again?
4821/01/201648.16533.11715.048
4922/01/201648.19633.23414.962
5023/01/201648.22634.40613.82
5124/01/201648.25634.52313.733
5225/01/201648.28634.64113.645
5326/01/201648.31735.10913.208
5427/01/201648.34735.22713.12
5528/01/201648.37748.377
5629/01/201648.40748.407
5730/01/201648.43848.438
5831/01/2016#N/A#N/A#N/A
5901/02/201648.49834.40614.092
6002/02/201648.52934.52314.006
6103/02/201648.55934.64113.918
6204/02/2016#N/A#N/A#N/A
Sheet2
Cell Formulas
RangeFormula
H9,H28H9=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:A24A15=A14+1
F47:F62,F28:F43,F9:F24F9=ABS(D9-C9)
H47H47=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.
 
Upvote 0
It worked on my example set!!

But for some bizarre reason not on my actual data set 🤷‍♂️

Thanks so much helping me! 👍

help.xlsx
ABCDEFGHIJKL
104/06/201748.13497#N/A#N/A
205/06/201748.16525#N/A#N/A
306/06/201748.19552#N/A#N/A
407/06/201748.2258#N/A#N/A
508/06/201748.25607#N/A#N/A
609/06/201748.28635#N/A#N/A
710/06/201748.31662#N/A#N/A
811/06/201748.34689#N/A#N/A
912/06/201748.37717#N/A#N/A
1013/06/201748.40744#N/A#N/A#N/A#N/A values in ranges breaks the formula
1114/06/201748.43772#N/A#N/AShould Return cell A640
1215/06/201748.467996415.532
1316/06/201748.498266314.5017
1417/06/201748.528546213.4715
1518/06/201748.55881501.44119
1619/06/201748.58909490.41091
1720/06/201748.61936#N/A#N/A
1821/06/201748.64964#N/A#N/A
1922/06/201748.67991#N/A#N/A
2023/06/201748.71018#N/A#N/A
Sheet1
Cell Formulas
RangeFormula
H10H10=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:F20F1=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))


1700202284717.png
 
Upvote 0
I'm not sure why the formula won't work on this range set

Book2
ABCDEFGHIJKLM
104/06/201748.134973315.135
205/06/201748.1652533.1171915.0481
306/06/201748.1955233.2343814.9611
407/06/201748.225834.4062813.8195
508/06/201748.2560734.5234713.7326
609/06/201748.2863534.6406713.6457
710/06/201748.3166235.1094313.2072
811/06/201748.3468935.2266213.1203
912/06/201748.3771735.3438113.0334
1013/06/201748.4074435.46112.9464#N/A#N/A values in ranges breaks the formula
1114/06/201748.4377235.5781912.8595<<min diffShould return Cell A11
1215/06/201748.4679934.2890914.1789
1316/06/201748.4982634.4062814.092
1417/06/201748.5285434.5234714.0051
1518/06/201748.5588134.6406713.9181
1619/06/2017#N/A#N/A#N/A
1720/06/201748.6193635.2266213.3927
1821/06/201748.6496435.3438113.3058
1922/06/201748.6799135.46113.2189
2023/06/201748.7101835.5781913.132
Sheet1
Cell Formulas
RangeFormula
H10H10=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:F20F1=ABS(D1-C1)
Press CTRL+SHIFT+ENTER to enter array formulas.
try this


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

1700202434605.png
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top