Return the last values below / after a Specific Value

S

Sam

New member
Joined
Apr 3, 2014
Messages
36
Reaction score
0
Points
0
Excel Version(s)
2007
Hi All,

I am using Excel 2007 for Windows.

I have had assistance with a similar scenario, and have tried to adjust the formula but I am not getting the results I expect.

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 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 relative 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 the last instance of a specific numeric value (criterion) within “Data”.
From that instance, I would then like to return between 1 and more of the values located below/after the last instance of the specified criterion.

1. Find the last instance of a specific criterion within “Data”.
2. Based on last instance of the specific criterion, return the last value(s) below/after that last 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 if no values available to be returned; return the values to separate cells across a single row.

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


Expected Results:
Looking for last criterion of 0 (zero) in table –
Found in column “V”, row 14; return value(s) below/after criterion = 2

Looking for last criterion of 4 in table –
Found in column “AA”, row 11; return value(s) below/after criterion = 5

Looking for last criterion of 7 in table –
Found in column “V”, row 13; return value(s) below/after criterion = 0, 2

Looking for last criterion of 14 in table –
Found in column “P”, row 18; return value(s) below/after criterion = nothing, empty text(“”)

Hope you can help.

Thanks,
Sam
 

Attachments

  • Book5a.xlsm
    19.4 KB · Views: 15
In the attached there are formulae like:
=LastValues($H2,Data,I$1,TRUE)
where:
=LastValues(ValueSought,DataRange,AboveOrBelow,OneCellResult)

I've included stuff like AboveOrBelow (you supply a string being "Above" or "Below", or a reference to a cell containing that string) because of your comment in cell A10 of Sheet1,
and stuff like OneCellResult because you have both kinds of result in your sheet (here you supply TRUE or FALSE or a reference to a cell with TRUE or FALSE).
I can remove them if you want.

I supplied a udf rather than a formula because (a) you already had a macro/function and (b) it's a lot easier to enter and maintain than a megaformula.

Note that a line in the udf is:
Set rngData = DataRange.Offset(1).Resize(DataRange.Rows.Count - 1)
which is there to lop the top row off your named range Data.

I'm sure I could make the code slicker, only I've run out of time.
 

Attachments

  • ExcelGuru2987Book5a.xlsm
    29.1 KB · Views: 13
I really do appreciate you taking the time to provide me with this great udf solution. It really is brilliant! Thank you.


I supplied a udf rather than a formula because (a) you already had a macro/function and (b) it's a lot easier to enter and maintain than a megaformula..[/QUOTE said:
If possible would still appreciate it if you could provide a formula.


Thank you,
Sam
Click to expand...
 
Well, I've been trying but failing.
Perhaps others may know of a way.

In the attached version of the previous file I posted I've tried developing a formula and got to cell AK20 (working on the same sheet as the table). This uses a helper column AJ.
I can't do that final step of incorporating the formula in column AJ into the final mega-formula.

I wouldn't like to maintain a megaformula such as this.
 

Attachments

  • ExcelGuru2987Book5a2.xlsm
    31.3 KB · Views: 10
Thank you ever so much for all your help, and for taking the time to attempt the mega-formula.

Hope someone can help.

Sam
 
See if this array formula does it for you

=ADDRESS(MAX(IF(Data=$A$1,ROW(Data))),MAX(IF(INDEX(Data,MAX(IF(Data=$A$1,ROW(Data)))-ROW(Data)+1,0)=$A$1,COLUMN(Data))),4)

where Data is the grid, A3:AD18, and $A$1 holds the value to lookup
 
Last edited:
Hi Bob,

Bob Phillips said:
See if this array formula does it for you

=ADDRESS(MAX(IF(Data=$A$1,ROW(Data))),MAX(IF(INDEX(Data,MAX(IF(Data=$A$1,ROW(Data)))-ROW(Data)+1,0)=$A$1,COLUMN(Data))),4)

where Data is the grid, A3:AD18, and $A$1 holds the value to lookup
Click to expand...

Thank you for assistance, but this array formula returns the cell address of the last instance of the lookup value, and not the value below / after it which is what I need. Your help is appreciated.

Sam
 
Assuming you enter the column number of interest in say AG2 (i.e. 16 for "V") and the value to find in AH2 (i.e. 7), then try:

=IFERROR(INDEX(INDEX($G$4:$AD$18,0,$AG$2),SMALL(IF(INDEX($G$4:$AD$18,0,$AG$2)<>"",IF(ROW($G$4:$AD$18)-ROW($G$4)+1>LARGE(IF(INDEX($G$4:$AD$18,0,$AG$2)=$AH$2,ROW($G$4:$AD$18)-ROW($G$4)+1),1),ROW($G$4:$AD$18)-ROW($G$4)+1)),ROWS($A$1:$A1))),"")

confirmed with CTRL+SHIFT+ENTER, not just ENTER

and copied down 15 cells (to match table height).
 
Thank you for assistance. Unfortunately, when I apply the formula (using CTRL+SHIFT+ENTER), I get empty text ("") returned as the results.

NBVC said:
Assuming you enter the column number of interest in say AG2 (i.e. 16 for "V") and the value to find in AH2 (i.e. 7), then try:

=IFERROR(INDEX(INDEX($G$4:$AD$18,0,$AG$2),SMALL(IF(INDEX($G$4:$AD$18,0,$AG$2)<>"",IF(ROW($G$4:$AD$18)-ROW($G$4)+1>LARGE(IF(INDEX($G$4:$AD$18,0,$AG$2)=$AH$2,ROW($G$4:$AD$18)-ROW($G$4)+1),1),ROW($G$4:$AD$18)-ROW($G$4)+1)),ROWS($A$1:$A1))),"")

confirmed with CTRL+SHIFT+ENTER, not just ENTER

and copied down 15 cells (to match table height).
Click to expand...

Can I just confirm that your cell AH2 holds the last instance of the value to find, and that the formula should then return the value that is located below /after that specific last instance of the value in AH2. Any further help most appreciated.

Sam
 
See attached.

AH2 holds the value that you want to find in order to get the values below it in the select column (entered in AG2)
 

Attachments

  • Book5a.xlsm-1.XLS
    18.1 KB · Views: 12
Apologies for not getting back to you sooner. The error was all mine, I inadvertently moved some data which created the problem.

NBVC said:
See attached.
AH2 holds the value that you want to find in order to get the values below it in the select column (entered in AG2)
Click to expand...

No that I've corrected my problem your formula works like a dream! Absolutely brilliant!

=IFERROR(INDEX(INDEX($G$4:$AD$18,0,$AG$2),SMALL(IF(INDEX($G$4:$AD$18,0,$AG$2)<>"",IF(ROW($G$4:$AD$18)-ROW($G$4)+1>LARGE(IF(INDEX($G$4:$AD$18,0,$AG$2)=$AH$2,ROW($G$4:$AD$18)-ROW($G$4)+1),1),ROW($G$4:$AD$18)-ROW($G$4)+1)),ROWS($A$1:$A1))),"")

confirmed with CTRL+SHIFT+ENTER, not just ENTER
and copied down 15 cells (to match table height).

Thank you,
Sam
 
You must log in or register to reply here.
Back
Top