MATCH does not equal

[brett.kaplan]

Is there a way to use MATCH to return the item in an array where it
does not equal a value?

For instance, if my list were:

0 0 0 5 6 2 3 8

I want the formula to return 4, since that is the first time it does
not equal 0.

I can't use nested IFs because it may run over the nesting limit.

Thanks!

 

[Max]

One way
Assuming source data in A1:H1,
Place in say, J1, array-enter by pressing CTRL+SHIFT+ENTER:
=MATCH(TRUE,A1:H1>0,0)

 

[Dave Peterson]

This formula will return the position (position 4 in your sample) in the range
of the first non-zero entry:

=MATCH(TRUE,1:1<>0,0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

This will return the value (5 in your sample):
=index(1:1,MATCH(TRUE,1:1<>0,0))

(Still an array formula)