# Thread: Blank Cells Match Formula Explanation

1. ## Blank Cells Match Formula Explanation

Rory posted a brilliant solution to looking for blank cells in a column. The formula is:

=MATCH(TRUE,INDEX(ISBLANK(\$A\$3:\$Q\$3),0),0)

It works and is very elegant. I've been looking for something like this for a long time. My difficulty is I cannot understand the details and suspect there's an area of Excel I know little about. For example, why is there a zero in the INDEX command? Could someone please help me with an explanation or suggest a reference where I can learn more about it.

Regards

2. INDEX really isn't required here at all, it's used in this case to avoid having to "array enter" the formula. This version works OK if you confirm the formula with CTRL+SHIFT+ENTER

=MATCH(TRUE,ISBLANK(\$A\$3:\$Q\$3),0)

When you use INDEX with zero as 2nd argument (assuming first argument is a single column or single row range) then that simply tells excel to return the whole row/column, so adding INDEX changes nothing.....except the need for "array entry".

Excel help explains the INDEX part, e.g. if you use

=INDEX(Range,1,0)

that will return the whole of the 1st row of range. Of course to make sense that will normally need to be used in another function that can process a range

regards, barry

3. ## The Following User Says Thank You to barry houdini For This Useful Post:

Antediluvian (2014-09-19)

4. Barry,

Thank you. Is there a generic way to express an "array enter" formula as a formula? The reason I need this is that I do a lot of coding using the old macro language and thus need the formulae, not the array enter equivalent.

Regards

5. Some formulas must be entered explicitly as an array (ie using the ctrl-shoft-enter). Some formulas implicitly work with arrays (sumproduct, index, etc) so the formula does not explicitly need to tell excel it is an array. I don't believe there is a "generic way", it will depend on the formula: I don't think that all formulas can be converted: some require creating an explicit array.

[The old macro language (ie pre-VBA) has been discontinued for nearly a decade, you may want to start converting, at some point excel will no longer support it...]
Steve

6. ## The Following User Says Thank You to sdckapr For This Useful Post:

Antediluvian (2014-09-19)

7. I can suggest TRIM and then test with LEN >0

8. ## The Following User Says Thank You to Supershoe For This Useful Post:

Antediluvian (2014-09-19)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•