match("",A1:A100,0)

Posted by zorro on October 19, 2001 7:35 AM

I can't use the match function to find blanks. Any suggestions.

Posted by Barrie Davidson on October 19, 2001 7:42 AM

Could you put an ISBLANK formula in column B [=ISBLANK(A1)] and then change your formula to:

=Match(True,B1:B100,0)

Regards,
BarrieBarrie Davidson

Posted by Mark W. on October 19, 2001 7:45 AM

Or...

{=MATCH(TRUE,ISBLANK(A1:A100),0)}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

Posted by IML on October 19, 2001 7:58 AM

doh!

Just for humor value (if any), when you get started down the wrong path, disasters like this occur:
=LARGE(((ISBLANK(A1:A100))*(ROW(1:100))),100-COUNTIF(A1:A100,"&LT;&GT;"&""))

I hate when that happens.