# Return bottom value in subsets where criteria <> 0

• I have 'Name' subsets in col A. If I want to find the first 'Back' value from the bottom of subset '1. Target Satin', I can use :
=INDEX(B2:B26,SUMPRODUCT(MAX(ROW(A2:A26)*(E2=A2:A26))-1))

in cells F2, F3 I want to add a condition to this and only return the first 'Back' value from the bottom of each subset that doesn't equal 0 in col C

## Files

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

I'm not a formula expert but this seems to do the trick. :cheers:

=INDEX(\$B\$2:\$B\$26,SUMPRODUCT(MAX((ROW(\$A\$2:\$A\$26)*((\$E2=\$A\$2:\$A\$26))-1)*(\$C\$2:\$C\$26>0))))

Bruce :cool:

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Thankyou skywriter for you correct and prompt response.

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

My pleasure, thanks for the feedback and reputation points. :cheers:

Bruce :cool:

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Another formula you may try to get the desired output...

Code
``=MAX(INDEX((\$A\$2:\$A\$26=E2)*(\$C\$2:\$C\$26<>0)*(\$B\$2:\$B\$26),))``

Regards.
sktneer

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Another good solution sktneer! In fact I'm kinda favouring your's more than the earlier one. There are some weird things happening with that formula on later trials, that I can't comprehend and getting an #N/A error? Seems more unstable? Thankfully you replied and all is good.

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Quote from rinconpaul;800422

Another good solution sktneer! In fact I'm kinda favouring your's more than the earlier one. There are some weird things happening with that formula on later trials, that I can't comprehend and getting an #N/A error? Seems more unstable? Thankfully you replied and all is good.

Maybe if you would care to share we could figure out the issue and all learn something.

Bruce :cool:

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

You're welcome rinconpaul! Glad it helped.
And thanks for the rep.

Regards.
sktneer

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Yes, I will skywriter. Thought you'd be too busy to bother and didn't want to be a pain. All I've done here is move the test data around and adjusted the formula to suit. Your formula is throwing up an answer from further down the column? Like I said previously, can't work out WHY? I've redone your formula in cells J9, 10 and the 'Max' formula alongside.

## Files

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Your formula should be like this...

In J9

Code
``=INDEX(\$F\$9:\$F\$33,SUMPRODUCT(MAX((ROW(\$E\$9:\$E\$33)-ROW(\$E\$9)+1)*(I9=\$E\$9:\$E\$33)*(\$G\$9:\$G\$33>0))))``

Regards.
sktneer

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Hello everyone ...

An alternative for cell J9 could be ...

Code
``=INDEX(\$F\$9:\$F\$33,SUMPRODUCT((\$E\$9:\$E\$33=I9)*(\$G\$9:\$G\$33>0))-1+MATCH(I9,\$E\$9:\$E\$33,0))``

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Apologies sktneer for the 'skywriter'! Was just going from memory when I wrote the reply and plucked the wrong name out of my brain.:0ops:

OK, well that's changed things a bit. Wonder why the old formula did work at one stage though?? :question: You've been very professional and kind in sorting the solution. Now I've got two correct answers. Cheering!...and now Carim has weighed in! Three Thankyou all :ole:

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

This part, (ROW(\$E\$9:\$E\$33)-ROW(\$E\$9)+1), makes the difference. As you can see when you use Row() function in this context, all you want to get the row numbers of your data set.
And the above will give you an array of row numbers 1:25.
While your previous formula gives you row nummbers 8:32 as you used ROW(\$E\$9:\$E\$33) and subtracted 1 from it. ROW(\$E\$9:\$E\$33) will give you an array of numbers 9:33 and after subtracting 1 from it, you will get array of numbers 8:32.
And in J10 where you got #Ref! error, the problem was the length of Array used in Index part is 25 and your Row_Number returned is 29 which obviously is not found in the list of 25 items and that's why you were getting an error there.

The same formula worked in your previous sample file because there the data started from Row2 so you get row number array starting from 1.

Hope that's not very confusing as I am not very good in explaning things. Lol

Regards.
sktneer

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Most of it went over my head:thanx:, but your explanation is on the forum forever now, should anyone else seek an answer. Well done again.

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Paul,

You are right ... explanations in the Forum are there to stay ... :wink:

The use of ROW() has two drawbacks :

1) It is dependent on the cell in which the initial formula is placed

2) It will give erroneous results ... should any rows be inserted within the range

To avoid these potential problems ... it is much safer to use ROW[COLOR="#FF0000"]S[/COLOR]()

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Thanks for the tip Carim. I'm about to find out, as the use of this formula will apply to a dynamic table?

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Here is another formula which you can try to get the same result.

Sample File1:

Code
``=INDEX(\$B\$2:\$B\$26,MATCH(1,INDEX((\$A\$2:\$A\$26=E2)*(\$C\$2:\$C\$26=0),),0)-1)``

Sample File2:

Code
``=INDEX(\$F\$9:\$F\$33,MATCH(1,INDEX((\$E\$9:\$E\$33=I9)*(\$G\$9:\$G\$33=0),),0)-1)``

## Files

Regards.
sktneer

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Overwhelmed by the helpful posts. What a great forum. Feelin loved :sing:

• Re: Return bottom value in subsets where criteria &lt;&gt; 0

Thanks for the feedback. :cheers:

Regards.
sktneer

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!