Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    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. #2
    New Lounger
    Join Date
    Sep 2014
    Posts
    1
    Thanks
    0
    Thanked 1 Time in 1 Post
    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. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #5
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    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)

Tags for this Thread

Posting Permissions

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