How to ignore N/A in array formula

[Raehan]

I have the following formula working fine if there is no N/A value between BY117:BY3753. But as I have vlookup , the forumula is not calculating the results as I have some N/A in that column. Is there a way that Excel ignore N/A and just calculate wherever the values are.

{=MIN(IF(BY5:BY3753=BY5,$CA$5:$CA$3753))}

Thanks,
Rae

[63falcondude]

Try this (changes in red):

=MIN(IF((ISNUMBER(BY5:BY3753))*(BY5:BY3753=BY5),$CA$5:$CA$3753)) Ctrl Shift Enter

Or you can include IFERROR into your VLOOKUP formula like this:

=IFERROR(formula,"")

Then you can use your MIN IF function as-is.

Edit: See below for corrections.

[ChemistB]

Easiest to avoid the N/A in the first place. Put your VLOOKUP inside an IFERROR function

=IFERROR(VLOOKUP(x,a:b,2,false), "") or

=IFERROR(VLOOKUP(x,a:b,2,false), "N/A") if you like having N/A
Then your arrayed formula should work.

[Jonmo1]

It won't work that way Falcondude.
Because the isnumber only traps the error within the isnumber.
But the error still exists in the *(BY5:BY3753=BY5)
So you still have the #N/A error included in the math.

You have to completely separate the error from the math with another IF..

=MIN(IF(ISNUMBER(BY5:BY3753),IF(BY5:BY3753=BY5,$CA$5:$CA$3753)))

[63falcondude]

Jonmo, I tested it here and it seemed to work.

See attached.

[Jonmo1]

It's also assuming the values in column BY are numeric. They may be textual values..

In that case

=MIN(IF(NOT(ISERROR(BY5:BY3753)),IF(BY5:BY3753=BY5,$CA$5:$CA$3753)))

[Jonmo1]

Your math isn't including the range that contains the #N/A
Formula from post #2 has the 2nd half of the multiplication is the same column that was tested for errors with isnumber.

=MIN(IF((ISNUMBER(BY5:BY3753))*(BY5:BY3753=BY5)

[63falcondude]

It's also assuming the values in column BY are numeric.

You're right, I didn't think of that.

Your math isn't including the range that contains the #N/A
Formula from post #2 has the 2nd half of the multiplication is the same column that was tested for errors with isnumber.

Okay, I understand now.

Sorry for any confusion.

[Raehan]

Great. Thank you so much everyone. Much appreciated.

[Jonmo1]

Glad to help.

[Raehan]

comment deleted as it was not required.