MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

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

Try this in A1:

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


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:


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.