Results 1 to 8 of 8

Thread: Index/Match for Nonblanks
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2011
    Location
    Boone, NC
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index/Match for Nonblanks

    Hello Everyone,
    I'm designing a sheet that is to be used as a tally for a strawman poll before a real vote is held on some projects being considered at my company. Some of these polls will have comments on them when returned. I'd like to list all comments, though most will be blank with no comments.

    I have attached a sample of the worksheet, but I cannot get my index/match (I am terrible at this formula base; vlookup is my comfort zone) to return all non-blanks, while ignoring the "Comments" header which is there to facilitate data entry.

    Can anyone please help me with this, and maybe the logic of how to understand index/match going forward?

    https://docs.google.com/spreadsheets...it?usp=sharing

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Index/Match for Nonblanks

    Hi,

    See if this works for cell Q8

    Code:
    =INDEX(E24:E323,MATCH(1,INDEX((E24:E323 <> "")*(ISERROR(SEARCH("Comments",E24:E323))),0,0),0))
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    New Member
    Join Date
    Aug 2011
    Location
    Boone, NC
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match for Nonblanks

    Quote Originally Posted by Aryatect View Post
    Hi,

    See if this works for cell Q8

    Code:
    =INDEX(E24:E323,MATCH(1,INDEX((E24:E323 <> "")*(ISERROR(SEARCH("Comments",E24:E323))),0,0),0))
    Aryatect,
    Thanks so much for your help! The formula in Q8 did pull the first comment, but did not continue pulling other comments below it. The formula simply kept returning the first comment only as I dragged the formula to Q9, Q10, etc. Thanks so much for the great start though, that's way more than I was getting.

  4. #4
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Index/Match for Nonblanks

    Hi,

    Try this

    Code:
    =IFERROR(INDEX($E$25:$E$325,AGGREGATE(15,6,(ROW($E$25:$E$325)-ROW($E$25)+1)/((MATCH($E$25:$E$325,$E$25:$E$325,0)=ROW($E$25:$E$325)-ROW($E$25)+1)*($E$25:$E$325 <> "")*(ISERROR(SEARCH("Comments",$E$25:$E$325)))),ROWS(Q$8:Q8))),"")
    in Q8 and copy it down
    Last edited by Aryatect; Jul 19th, 2019 at 01:51 PM. Reason: Added a line for copying
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    New Member
    Join Date
    Aug 2011
    Location
    Boone, NC
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match for Nonblanks

    Quote Originally Posted by Aryatect View Post
    Hi,

    Try this

    Code:
    =IFERROR(INDEX($E$25:$E$325,AGGREGATE(15,6,(ROW($E$25:$E$325)-ROW($E$25)+1)/((MATCH($E$25:$E$325,$E$25:$E$325,0)=ROW($E$25:$E$325)-ROW($E$25)+1)*($E$25:$E$325 <> "")*(ISERROR(SEARCH("Comments",$E$25:$E$325)))),ROWS(Q$8:Q8))),"")
    in Q8 and copy it down
    I can't thank you enough Aryatect. Do you recommend any resources to learn how to use this power?

  6. #6
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Index/Match for Nonblanks

    Hi Glad could help

    I am a big fan of Mike "excelisfun" Girvin. He has a youtube channel by name "excelisfun" with lots of information.
    Last edited by Aryatect; Jul 19th, 2019 at 02:26 PM.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  7. #7
    New Member
    Join Date
    Aug 2011
    Location
    Boone, NC
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match for Nonblanks

    Quote Originally Posted by Aryatect View Post
    Hi Glad could help

    I am a big fan of Mike "excelisfun" Girvin. He has a youtube channel by name "excelisfun" with lots of information.
    Thank you again, works perfectly!

  8. #8
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Index/Match for Nonblanks

    Thank you for the feedback
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

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
  •