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 we

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!

One way
Assuming source data in A1:H1,
Place in say, J1, array-enter by pressing CTRL+SHIFT+ENTER:
=MATCH(TRUE,A1:H10,0)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
...
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 we

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!

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)


wrote:

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 we

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!

--

Dave Peterson