Match Function Issue when Using Match Type 1

Question

Hi all,

I have a list of 1 and 0 values, and I'm using Match to find the first instance of 1 from bottom up. For example,

	A	B
1	1	1
2	1	2
3	0	2
4	0	2
5	1	5
6	1	6
7	0	6
8	0	6
9	0	6
10	0	6
11	0	6
12	0	6
13	0	13
14	0	14
15	0	15
16	0	16
17	0	17

The match function is in the second column, and the array that it searches would grow accordingly as the formula moves down. An example of the formula in cell B3 is "=MATCH(1,$A$1:A3,1)".

As you can see from the above example, it kept working until row 13, where it simply stopped looking and grabbed the number right next to it, thinking that's the closest number it can find. I cannot find this issue documented anywhere, and wonder if it is confined to my copy. I did try the same trick on another computer at work, which Excel exhibits the exact same behaviour.

The list cannot be sorted, and I'm using this function as I'm only interested in the very last occurence of 1 relative to the position of the function.

Thanks in advance.

Alan

Answer 1

Bill wrote:

One suitable formula would be

=MAX(ROW(A$1:A1)*A1)

entered as an array formula in B1 (using Ctrl+Shift+Enter) and copying down.

I believe that returns zero when column A is zero, specifically rows 3, 4 and 7-12.  Alan's example indicates that he wants to see 2 or 6 in those rows; i.e. the last 1 in previous rows.  He wrote that his MATCH function seemed to be "working until row 13".  (Of course, it was by luck.)

Answer 2

MATCH(...,1) requires a sorted list.  I think it uses a binary-chop method to find the desired value which is why it starts failing at row 13 - there are no 1s from row 7 onwards.

One suitable formula would be

=MAX(ROW(A$1:A1)*A1)

entered as an array formula in B1 (using Ctrl+Shift+Enter) and copying down.

Answer 3

Nice one, JoeU.

Answer 4

Alan wrote:

I have a list of 1 and 0 values, and I'm using Match to find the first instance of 1 from bottom up.

I got so caught up in explaining how MATCH works that I overlooked the "obvious" solution.

In B1, put the formula:

=IF(A1=1,ROW(),0)

Put the following formula into B2 and copy down:

=IF(A2=1,ROW(),B1)