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

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.

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

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

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.

Duly amended - very much appreciated!