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?
>Solution :
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.
