I have a formula =IFERROR(INDEX($A$1:$A$24, MATCH(MIN(ABS($B$1:$B$24)), ABS($B$1:$B$24),0)),"NA")
and it does not work when there is a cell in array containing non-numeric value. For instance, I have this data:
Column A | Column B |
---|---|
6.990 | -3.105 |
6.875 | -2.875 |
6.750 | -2.625 |
6.625 | -2.375 |
6.500 | -2.125 |
6.375 | -1.875 |
6.250 | -1.625 |
6.125 | -1.375 |
5.990 | -1.105 |
5.875 | -0.875 |
5.750 | -0.625 |
5.625 | -0.375 |
5.500 | -0.125 |
5.375 | 0.125 |
5.250 | 0.500 |
5.125 | 0.750 |
4.990 | 1.020 |
4.875 | 1.250 |
4.750 | 1.625 |
4.625 | 2.000 |
4.500 | 3.125 |
4.375 | 3.625 |
4.250 | 4.125 |
4.125 | 5.000 |
4.000 | NA |
Because I have NA in the last cell, The result of the formula will be #VALUE! How can I fix the formula so I does not lead to an error?
CodePudding user response:
Wrap the ABS part in IFERROR:
=INDEX($A$1:$A$25, MATCH(MIN(IFERROR(ABS($B$1:$B$25),99999)), ABS($B$1:$B$25),0))
Please note that some versions of Excel will require the use of Ctrl-Shift-Enter instead of Enter when exiting edit mode to force the array entry of the formula.