+ Reply to Thread
Results 1 to 13 of 13

Return non blank cell using INDEX and MATCH

  1. #1
    Registered User
    Join Date
    05-10-2006
    Posts
    59

    Return non blank cell using INDEX and MATCH

    Hi,

    I would like to try and edit this formula to return non blank cells for a dropdown list:

    Please Login or Register  to view this content.
    The range that I would like to omit the blanks from is Results!$A$2:$S$183.

    I also cannot use an array formula as I need to put this formula in the source field for the dropdown.

    Thanks

    *NB Please note that I have also posted this question in another forum but have not had a response so thought I would try here as well:

    https://www.mrexcel.com/forum/excel-...ml#post5320718

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Return non blank cell using INDEX and MATCH

    Not sure I understand. Since there is a value in A55 that you are looking to find in the Results Sheet. It will not return a blank, but will return a #NA if the value is not found. Please expand upon your desired results for what you are wanting.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-10-2006
    Posts
    59

    Re: Return non blank cell using INDEX and MATCH

    Hi alansidman,

    Thank you for having a look, I will try and explain it to make what I am trying to achieve clearer.

    I have a dropdown list in A55 and that result matches one of the headings in columns A1 to S1.

    The results for that column are returned and the range is A2 to S183.

    So for example...

    A55 = Heading 2

    Capture.PNG

    The dropdown list (dependant on the dropdown selection in A55) would then show the following options:

    5
    8
    2
    Blank
    4
    5
    2
    Blank

    I want it to omit the blanks and simply show 5,8,2,4,5,2 (or what ever is in that column).
    Last edited by mabbutt; 08-06-2019 at 09:39 PM.

  4. #4
    Registered User
    Join Date
    05-10-2006
    Posts
    59

    Re: Return non blank cell using INDEX and MATCH

    Please use the following link for an example of the problem:

    https://www.excelforum.com/attachmen...1&d=1565163480

    B10 contains the first dropdown and D10 is the output that I need to remove the spaces from.

    For example, if you select 'Test 6' in B10 then the output in D10 should be Output 1 and Output 5 with no blanks.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-10-2006
    Posts
    59

    Re: Return non blank cell using INDEX and MATCH

    I solved this problem by using named ranges and tables which is not ideal but it does work.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Return non blank cell using INDEX and MATCH

    Will you share your solution for the benefit of others who may encounter a similar situation.

  7. #7
    Registered User
    Join Date
    05-10-2006
    Posts
    59

    Re: Return non blank cell using INDEX and MATCH

    Quote Originally Posted by alansidman View Post
    Will you share your solution for the benefit of others who may encounter a similar situation.
    Hi alansidman,

    I didn't post a solution as I didn't think that my workaround really answered my original query but this is what I did...

    I defined each column as a named range and then using the same selection I converted the range to a table.

    I then made the named range refer to the newly created table.

    Finally I used this formula for the dropdown source:

    Please Login or Register  to view this content.
    It works but is far from ideal which is why I was hoping to tweak my original formula to have a far more elegant approach.

    Example here - https://www.excelforum.com/attachmen...1&d=1565245921
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Return non blank cell using INDEX and MATCH

    There is another possibility: duplicate your ranges, excluding blank values... and then use that as the DV. Interested??
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  9. #9
    Registered User
    Join Date
    05-10-2006
    Posts
    59

    Re: Return non blank cell using INDEX and MATCH

    Quote Originally Posted by Glenn Kennedy View Post
    There is another possibility: duplicate your ranges, excluding blank values... and then use that as the DV. Interested??
    Hi Glenn,

    I can do it that way as well but it is just another work around and gets messy as the data is duplicated.

    I can't believe that it is not possible to do it with the INDEX and MATCH functions I just have not been able to work it out unfortunately.

    If you have the time it would be interesting to see your idea as there is always an opportunity to learn new things!

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Return non blank cell using INDEX and MATCH

    I can't think of any way of doing it without duplication. So that's a challenge for tomorrow... when it's due to be very wet here. For now, check this out (I fiddled with it anyway).

  11. #11
    Registered User
    Join Date
    05-10-2006
    Posts
    59

    Re: Return non blank cell using INDEX and MATCH

    Quote Originally Posted by Glenn Kennedy View Post
    I can't think of any way of doing it without duplication. So that's a challenge for tomorrow... when it's due to be very wet here. For now, check this out (I fiddled with it anyway).
    Hi Glenn,

    The end result is obviously perfect and I find it interesting to see how the formulas work but I think that this way is slightly harder to maintain if you add data at any point to the original source.

    If I ever find the solution to the original issue then I'll post it here!

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Return non blank cell using INDEX and MATCH

    Not so. I made a bit more space on your sheet. Now it's fully automated.

  13. #13
    Registered User
    Join Date
    05-10-2006
    Posts
    59

    Re: Return non blank cell using INDEX and MATCH

    Quote Originally Posted by Glenn Kennedy View Post
    Not so. I made a bit more space on your sheet. Now it's fully automated.
    Hi Glenn,

    OK with that automated solution the duplicated data can actually work really well and can now be a hidden sheet.

    Thank you for taking the time to explore an alternative way to solve this and this is easily a better way than my fix!

    I have also learnt a lot looking through your formulas as well so that is a big plus also.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Index Match return first non blank value
    By nhoj in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-12-2018, 12:34 AM
  2. [SOLVED] Need Index/Match to find the first non-blank cell and return the value in the top row
    By royalbluestuey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2018, 01:43 PM
  3. 2-dimensional index match, return nth non-blank value?
    By JMB10101 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-29-2016, 01:55 AM
  4. [SOLVED] IF <=0 INDEX MATCH LARGE otherwise return blank
    By augr in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 11-14-2016, 05:34 PM
  5. Index/ Match Formula, How to return blank cell as a blank not 0
    By MDResearcher in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-24-2016, 08:40 PM
  6. [SOLVED] IF date value returned from Index/Match is blank, return prior cell that returns a value
    By gunnerau in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-11-2014, 08:40 PM
  7. Index/Match to return a blank cell
    By Nick_in_Dubai in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-26-2012, 06:36 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1