Not displaying #N/A within an Array formula (Excel 97)

Topic

New Reply

I am using the following formula to look up the combination of two cells to look up the two matching cells on another sheet, and then return the balance in another cell:

{=INDEX(Sheet1!$F$11:$F$14,MATCH(Sheet2!C11&Sheet2!D11,(Sheet1!$C$11:$C$19&Sheet1!$D$11:$D$19),0),1)}

It’s returning #N/A when it can’t find a match, which I would expect it to do. I want it to display a 0 instead of #N/A if it can’t find a match. I’ve used the ISERROR nested function to do that before, but I cannot figure out how to incorporate it into this array formula. Any ides? Thanks! (file attached)

-Kelley

Reply | Quote

Replies

Your answer was in a recent WOW mailer but I can’t remember which one (2 or 3 issues back). You use “=IF(ISNA(Your Formula),0,(Your Formula))”.

Reply | Quote

This works for me:

=IF(ISERROR(INDEX(Sheet1!$F$11:$F$14,MATCH(Sheet2!C11&Sheet2!D11,(Sheet1!$C$11:$C$19&Sheet1!$D$11:$D$19),0),1)),0,INDEX(Sheet1!$F$11:$F$14,MATCH(Sheet2!C11&Sheet2!D11,(Sheet1!$C$11:$C$19&Sheet1!$D$11:$D$19),0),1))

Reply | Quote

Legare,

That worked, I just needed to play with it a bit more, I think I was not including the right number of parenthesis, and that was throwing the formula off.

Thanks for the help!

-Kelley

Reply | Quote

Wide enough post Legare?

Reply | Quote

For this answer. Unfortunately, since there is no continuation character for formula, I don’t know of a non-confusing way to break a formula that can be copied and pasted into a cell.

Reply | Quote

It’s okay – just teasing – much better than saying “Holy ” – look how long that formula is!

Ooops! now I said it.

Reply | Quote