I've half got this worked out. The formula I have in cell E7: =INDEX(B5:B14,MATCH(TRUE,INDEX(C5:C14<>0,),0)) finds the first price from the bottom row where the Vol <>0. I want to add another criteria and find the first price from the bottom of subset 'Ben' where Vol <>0. Answer would be 7.
Thanks
Find first value <>0 in subset

rinconpaul 
September 25, 2017 at 12:02 AM 
Thread is marked as Resolved.



Re: Find first value <>0 in subset
Try this...
1) First price from the bottom where Volume <>0,
2) First price from the bottom where Volumn <>0 and Name is Ben.

Re: Find first value <>0 in subset
Hi
Try
=INDEX(C5:C14,MATCH(1,IF(A5:A14="Ben",IF(B5:B14<>0,1)),1))
This is an array formula. [arf]*[/arf]

Re: Find first value <>0 in subset
Thanks guys. Amazing how many different ways to get the same result? I couldn't add more stars to your reputation sktneer, as I've already awarded you stars for your earlier efforts, but thanks again, you're very helpful. :yourock:

Re: Find first value <>0 in subset
FWIW you can also use LOOKUP
=LOOKUP(2,1/($A$5:$A$14="Ben")/($B$5:$B$14<>0),$C$5:$C$14)


Re: Find first value <>0 in subset
Thanks Rory
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!