Ignoring a blank cell using an index match - GoogleSheets
Results 1 to 2 of 2

Thread: Ignoring a blank cell using an index match - GoogleSheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Ignoring a blank cell using an index match - GoogleSheets

    Hi,

    I am using an index match formula that needs to ignore blanks.

    Originally I was using the following formula:
    =iferror(index('secret'!$E:$E,match($B12,'secret'!$L:$L,0)),"")

    My data has multiple repeats within the L column but sometimes the corresponding E value is blank and despite the next L repeat having content within the E cell, a blank will be returned unless i change the index match formula.
    I have an if formula stating that if a blank is found, to carry on doing the formula from a point which I can specify. So for example if a blank match was found at row 5, I would want to have the formula keep working from row 6.

    =IF(index('secret'!$E:$E,match($B13,'secret'!$L:$L,0))="",index('secret'!$E33:$E,match($B13,'secret'!$L:$L,0)),index('secret'!$E:$E,match($B13,'secret'!$L:$L,0)))

    The above formula works since I'd found the blank row (using another formula - see below) to be row 32. I have it looking from row 33 if a blank was found. Ideally, I wouldn't need to manually put in the 33 and could just insert the below formula in place of the 33;

    ROW(index('secret'!$E:$E,match($B13,'secret'!$L:$L,0)))+1

    How might I combine the two?

    Also, if there is a better way of doing an index match which ignores blank values, I'd very much like some input as this seems slightly convoluted. This is all on Google sheets btw in case thats relevant.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Ignoring a blank cell using an index match - GoogleSheets

    Welcome to the MrExcel board!

    Quote Originally Posted by kennet View Post
    This is all on Google sheets btw in case thats relevant.
    It is extremely relevant since you had posted your question in the Excel Questions forum.
    Hence it has been moved to the "General Excel Discussion & Other Questions" forum
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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