Results 1 to 5 of 5

Thread: Find a specific criterion and Return the next non-blank value after each criterion

  1. #1

    Find a specific criterion and Return the next non-blank value after each criterion



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I am using Excel 2007 for Windows.

    I have a table that consists of many columns and expanding rows; for this reason, I have referenced the table as a dynamic named range called “Data”. “Data” can contain duplicate values in both columns and rows. I have numeric column labels in row 3, spanning the width of my table; “Data” starts in column “G”, row 4. Column “A” contains sequential numeric values (references) that can be used as table row numbers (for my table). The cells within the table are populated with a formula that returns either a numeric value or empty text (“”) showing a blank cell. Zero is a valid numeric value in the table.

    Scenario:
    Find multiple instances of a specific numeric value (criteria) within “Data”.
    From that instance, I would then like to return between 1 and more of the non-blank values located below/after each instance of the specified criteria.


    1. By table row number and table column label find each instance of a specific criterion within “Data”.
    2. Based on each found instance of the specific criterion, return the value(s) below/after that instance. The number of values to return will vary, and there may be no values to return.
    3. Using dynamic name “Data” in a flexible formula based solution with an input cell for the criterion; error trap (return empty text “”) if no values available to be returned; return the values to separate cells across a single row.

    The table row and column label co-ordinates for where the criteria are located in the table are already listed in the workbook. The formulae that provide the co-ordinates are also listed in the workbook.

    Sample Layout of table "Data":
    Book9c.xlsx attached


    Expected Results:
    Looking for criterion of 0 (zero) in table –

    Found in table row 1, column label 3; return value below/after criterion = 3
    Found in table row 1, column label 5; return value below/after criterion = 3
    Found in table row 1, column label 14; return value below/after criterion = 3
    Found in table row 1, column label 22; return value below/after criterion = 6
    Found in table row 6, column label 3; return value below/after criterion = Empty Text (“”)
    Found in table row 11, column label 16; return value below/after criterion = 2
    Found in table row 16, column label 1; return value below/after criterion = 4
    Found in table row 16, column label 18; return value below/after criterion = 0
    Found in table row 17, column label 9; return value below/after criterion = 7
    Found in table row 17, column label 18; return value below/after criterion = 6
    Found in table row 18, column label 22; return value below/after criterion = 11
    Found in table row 19, column label 15; return value below/after criterion = 7
    Found in table row 22, column label 1; return value below/after criterion = 6
    Found in table row 25, column label 12; return value below/after criterion = 4
    Found in table row 25, column label 15; return value below/after criterion = 1
    Found in table row 26, column label 18; return value below/after criterion = Empty Text (“”)
    Found in table row 27, column label 1; return value below/after criterion = Empty Text (“”)
    Found in table row 27, column label 24; return value below/after criterion = Empty Text (“”)


    Hope you can help.
    Attached Files Attached Files
    Thank you,
    Sam

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =IFERROR(INDEX(INDEX(Data,AE35+2,AF35):INDEX(Data,MAX(ROWS(Data)),AF35),MATCH(TRUE,ISNUMBER(INDEX(Data,AE35+2,AF35):INDEX(Data,MAX(ROWS(Data)),AF35)),0)),"")

    confirmed with CTRL+SHIFT+ENTER and copied down


  3. #3
    Just perfect! Thank you for all your time and effort taken to provide me with a great formula solution.
    Thank you,
    Sam

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    My Pleasure

    Btw,

    Something minor, but I wanted to mention it. The MAX() function is actually not needed. I wasn't thinking correctly at the time.

    you can use instead:

    =IFERROR(INDEX(INDEX(Data,AE35+2,AF35):INDEX(Data,ROWS(Data),AF35),MATCH(TRUE,ISNUMBER(INDEX(Data,AE35+2,AF35):INDEX(Data,ROWS(Data),AF35)),0)),"")

    Less functions to process is better.
    Last edited by NBVC; 2014-06-06 at 12:48 PM.


  5. #5
    Duly amended - very much appreciated!
    Thank you,
    Sam

Posting Permissions

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