+ Reply to Thread
Results 1 to 6 of 6

Match - Finding the next non-blank cell

  1. #1
    Registered User
    Join Date
    02-10-2011
    Location
    Liverpool, England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Match - Finding the next non-blank cell

    Hi!
    I'm looking for a formula that would give me the row number of the second or next non-blank cell in a column, based on an input value in another cell.
    Basically, what I'm looking for is a formula that will return the row number of the next non-empty cell that is greater than the reference value in cell E1. In this case it would be the row containing 181 = 8. If I were to input 200 in E1, the formula would return the row containing 273 = 11, etc.
    excel.PNG

    I've already managed to find the first non-empty cell containing the first value lower than cell E1 with the MATCH formula using MATCH TYPE=1, but I can't use MATCH TYPE=-1 because the list would have to be sorted in a descending order, which would render the previous formula useless.
    Thanks!
    Attached Images Attached Images

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Match - Finding the next non-blank cell

    Hi
    Use the following formula and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The row is the row of Excel sheet (not the table)
    You can adjust $A$2:$A$1000 instead $A$1:$A$1000 to get the row of a table that as first row equal to 2.

  3. #3
    Registered User
    Join Date
    02-10-2011
    Location
    Liverpool, England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Match - Finding the next non-blank cell

    Hi Jose, thanks for the answer. It works like a charm in my test spreadsheet, but when I adjusted it for my table, I keep getting "43" as the result, even though there are only 36 rows in my table.

    The table in question is located in the M and N columns where the following corresponds to my test table (in the screenshot):
    A1 = M20
    A2 = M21
    Last row in table = M55
    E1 = B8

    I got the following formula:
    Please Login or Register  to view this content.
    Any suggestions where I might be going wrong?

  4. #4
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Match - Finding the next non-blank cell

    Try this to get the rows of your table (from 21 to 55)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-10-2011
    Location
    Liverpool, England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Match - Finding the next non-blank cell

    Hmm... that gives the last non-blank cell instead of the next non-blank. In the mean time, I've figured out why the result was 43 before. The next non-blank cell was in the 43rd row of the spreadsheet. So I just adjusted the formula a little to subtract the row number of where my table starts (row 22) from the spreadsheet number (with an offset of 1) and I got exactly what I wanted. Thank you once again for your help, I had no idea about the AGGREGATE formula.
    Please Login or Register  to view this content.

  6. #6
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Match - Finding the next non-blank cell

    I am happy to have helped and thanks for the feedback.

+ 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 Finding First Non Blank
    By mphillips in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-08-2016, 05:27 PM
  2. 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
  3. Finding the first non blank cell
    By Elainefish in forum Excel General
    Replies: 6
    Last Post: 11-24-2015, 02:39 AM
  4. Replies: 0
    Last Post: 09-21-2013, 09:03 PM
  5. Finding blank cells and pasting below each blank cell
    By lilshaq in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2011, 03:50 AM
  6. Finding the last non blank cell in a row
    By a94andwi in forum Excel General
    Replies: 10
    Last Post: 11-07-2006, 08:38 AM
  7. Finding blank cell
    By Mario_Party in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2006, 12:15 PM

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