Skip blank or #N/A cells within array formula

Question

we.tl/t-bdhFMMp468

Could anyone help me with this formula please.

I'm comparing columns C and D, to find the smallest difference between both columns, then I'm returning it's position from column A

I've uploaded an example to clarify. The formula works in the first example!, but unfortunately breaks down in the second example, once error cells are found within either range.

I'm trying to figure out how I can get it to work regardless of blanks or #N/A error cells

=INDEX(A1:A6,MATCH(MIN(ABS(C1:C6-D1:D6)),ABS(C1:C6-D1:D6),0))

Ctrl alt shift.

Above formula works, but breaks down if their are error cells in ranges C1:C6 or D1:D6

Below formula ignores the error cells and returns the smallest difference between ranges between C and D, but It doesn't return the position from column A and I can't seem to figure it out =(

=MIN(IF(NOT(ISNA(ABS(C1:C6-D1:D6))),ABS(C1:C6-D1:D6)))

Ctrl alt shift.

Answer 1

In Excel, if you want to skip blank or #N/A cells within an array formula, you can use the IF and ISNA functions in combination with the array formula.

Here's an example:

Let's say you have a range of values in cells A1:A10, and you want to sum only the non-blank and non-#N/A cells:

=SUM(IF(ISNA(A1:A10), 0, IF(A1:A10<>"", A1:A10)))

This is an array formula, so you need to enter it using Ctrl+Shift+Enter instead of just Enter. If done correctly, Excel will surround the formula with curly braces, {}.

Here's a breakdown of the formula:

• ISNA(A1:A10): Checks if each cell in the range is #N/A.

• IF(ISNA(A1:A10), 0, ...): If a cell is #N/A, replace it with 0.

• IF(A1:A10<>"", A1:A10): Checks if each cell is not blank, and if true, keeps the original value.

• SUM(...): Finally, sums up the values.

This formula will sum only the non-blank and non-#N/A cells in the specified range; adjust the range according to your actual data.