locating "value not available" (#N/A) cells

Posted by mabaud on January 02, 2001 2:09 AM

I need to locate #N/A cells from column B. I would like to have the row number of the N/A cell appear in cell a1 of my sheet.
isna(b:b) works well, but how can I use match and isna?
thanks for your help
mabaud

Posted by cpod on January 02, 2001 7:09 AM

Try this in A1:
{=IF(MAX(N(ISNA($E$1:$E$23)=TRUE)*ROW($E$1:$E$23))>0,MAX(N(ISNA($E$1:$E$23)=TRUE)*ROW($E$1:$E$23)),"")}

This is an array formula, so enter it using Control+Shift+Enter

Posted by Aladin Akyurek on January 02, 2001 9:34 AM

Suppose that you need to use the MATCH function in some (array) formula like

=INDEX(C:C,MATCH(A1&B1,$A$1:$A$3&$B$1:$B$3,0))

Copied to range, this may lead to one or more #N/A.

Such an outcome can be controlled by rewriting the above formula as follows:

=IF(ISNA(MATCH(A1&B1,$A$1:$A$3&$B$1:$B$3,0)),"NoValue",INDEX(C:C,MATCH(A1&B1,$A$1:$A$3&$B$1:$B$3,0)))

You may use anything informative instead of NoValue that figures in the formula.

If you don't want to change #N/A producing formula in B:B, you might use
A1 =IF(NOT(OR(ISTEXT(B1),ISNUMBER(B1))),ROW(),"") [ copy down as far as needed ]

Hope this helps.

Aladin