MATCH

[Bill Brehm]

Hi,

I was trying to use the MATCH function to find the first positive value in a
vertical range. The problem is, my data is accending in the beginning, but
may fall and rise again. I hoped that MATCH (with match type 1) would just
scan up the list of values, but it must be doing something more clever, like
a binary search. It returns the first position after the entire range.

Can someone suggest a way to do what I need?

Thanks,

Bill

 

[Anon]

Hi,

I was trying to use the MATCH function to find the first positive value in a
vertical range. The problem is, my data is accending in the beginning, but
may fall and rise again. I hoped that MATCH (with match type 1) would just
scan up the list of values, but it must be doing something more clever, like
a binary search. It returns the first position after the entire range.

Can someone suggest a way to do what I need?

Thanks,

Bill

From Help on MATCH:
· If match_type is 1, MATCH finds the largest value that is less than or
equal to lookup_value. Lookup_array must be placed in ascending order:
....-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
So you cannot use match type 1 (approximate match) unless the data is
ascending monotonically.

To find the position (row number) of the first positive number, you could
use:
=MIN(IF(A1:A100>0,ROW(A1:A100)))

To find its value you could use:
=INDEX(A1:A100,MIN(IF(A1:A100>0,ROW(A1:A100))))

Note that these are array formulas, so have to be entered using
CTRL+SHIFT+ENTER rather than just ENTER.
(Substitute >= for > if you want to include zero.)