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

Thanks to all for your super fast response, I'm sure coming back in the future, or even try helping other if I can.

I didn't know that the MATCH function uses a binary search, thought it would be using a bubble search, and without knowing another function that would do a bottom up search (which is confirmed by joeu2004), I decided that I need to overhaul my method.

I went on to modify a significant chunk of my method to get my answer, and therefore avoid having the function to look for things. It is similar to what Bill has suggested, so I would mark Bill's answer as the best one here, but regardless, still heaps of thanks to all of you.

Answer 4

Alan wrote:

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.

[....]

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

The MATCH help page for XL2003, it states:

"If match_type is 1, [....] Lookup_array must be placed in ascending order".

Obviously, the values in column A ("random" 1s and 0s) are not in ascending order.

When the lookup column is not in ascending order, the results are somewhat unpredictable, owing to the nature of a binary search.  Sometimes you get lucky and MATCH returns what you expect.  Sometimes MATCH returns an unexpected number because match type 1 "finds the largest value that is less than or equal to lookup_value".  Sometimes MATCH returns a #N/A error.

As you may know, MATCH(1,$A$1:A3,0) -- note the zero in the 3rd parameter -- would find the first 1 from the top.

AFAIK, there is no reliable MATCH formula to find the first 1 from the bottom.  You could write a VBA UDF to do it.  The following is bare-bones.

Option Explicit

Function matchRev(lookupVal, lookupTable As Range)

Dim n As Long, i As Long

n = lookupTable.Count

If lookupTable.Rows.Count = 1 Then

   For i = n To 1 Step -1

      If lookupVal = lookupTable.Cells(1, i) Then matchRev = i: Exit Function

   Next

ElseIf lookupTable.Columns.Count = 1 Then

   For i = n To 1 Step -1

      If lookupVal = lookupTable.Cells(i, 1) Then matchRev = i: Exit Function

   Next

End If

matchRev = CVErr(xlErrNA)

End Function

Answer 5

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

Instead of repeating what Bill wrote, I'll add some complementary information.  You were lucky that MATCH seemed to work as long as it did.  Change the 1 in row 5 to zero and the function would have failed for that row.

The binary search (or binary chop as Bill called it) method compares the value at the mid point of the data array with the target value.  Then, it decides whether to restrict the search to the first or the 2nd half of the array.  It repeats this halving process until it either runs out of space (in which case no exact match was found) or it finds an exact match.