# Find first value <>0 in subset

• 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

## Files

• Re: Find first value &lt;&gt;0 in subset

Try this...

1) First price from the bottom where Volume <>0,

Code
1. =INDEX(C5:C14,MAX(INDEX((B5:B14<>0)*(ROW(A5:A14)-ROW(A5)+1),)))

2) First price from the bottom where Volumn <>0 and Name is Ben.

Code
1. =INDEX(C5:C14,MAX(INDEX((B5:B14<>0)*(A5:A14="Ben")*(ROW(A5:A14)-ROW(A5)+1),)))

Regards.
sktneer

• Re: Find first value &lt;&gt;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 &lt;&gt;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 &lt;&gt;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)

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why