1

I'm trying to do what should be a simple enough task: find the index of the last cell in an array that matches a certain value.

I am using a MATCH-INDEX function combination that is giving me incorrect, inconsistent results. I can't figure out the problem.

In this example I'm using an array with values of 1 or -1 and trying to find the index of the last 1 in the array.

Photo of index-match results

My understanding is (and using the Evaluate Formula tool confirms), INDEX(A1:F1=1,0) should return an array of

{FALSE,TRUE,FALSE,TRUE,FALSE,FALSE}

Why does the MATCH(TRUE,...) not give a result of 4 when it is fed this index array? MATCH works backwards in the array and should return the index of the last match, which is the TRUE value in the 4th position.

But here this code is giving a result of 6.

To make matters worse, if I use the same code but change around the array fed into INDEX, the results are inconsistent. When I'm changing array values to 1 or -1, sometimes the formula result changes and sometimes it doesn't, and I can't figure out why.

Below, changing only the 3rd array value changes the result of the formula from 4 to 6. WHAT IS GOING ON?!

Photo of index-match results (2nd array configuration)

Jordan
  • 4,424
  • 2
  • 18
  • 32
Luke
  • 11
  • 1
  • 1
    [Match](https://support.office.com/en-us/article/MATCH-function-e8dffd45-c762-47d6-bf89-533f4a37673a?ui=en-US&rs=en-US&ad=US) has a third parameter `match_type` and if that parameter is 1 or omitted, then: "MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE." – Axel Richter Oct 19 '16 at 08:55
  • @AxelRichter if that's the case then it seems I can't use MATCH to accomplish what I'm trying to do. Can you suggest a way that I can search through this array and find the last instance of a 1 or -1? Alternatively, I can change the numbers to "Yes" and "No" if string searching is easier. – Luke Oct 19 '16 at 18:33

2 Answers2

0

It seems that match only finds first, but not last.

But I have a possible solution, I would do it the following way:

expect: your values are between H4 AND H27, the value you look for is "39"

1: find the rownum for all matching cells

I4 --> I27: IF(H4 = 39; ROW(H4); -1)

2: find the max rownum in I4 --> I27

MAX(....)

3: subtract the rownum of first cell (zero- based index)

MAX(....) - ROW(H4)

4: to get it in only one cell put into a Matrix function (CONTROL SHIFT ENTER makes CURLY BRACKETS)

{=MAX(IF($H$4:$H$27=39;ROW(H4:H27);-1))-ROW(H4)}

sounds strange, but works :)

am2
  • 380
  • 5
  • 21
0

Index doesn't return an Array, but a number, as the name tells you, the "INDEX" of the element, which fulfills your question. With the last Parameter you can decide, whether it has to be exact or could be the next smaller or next bigger value (last both options will only work with sorted Input)

You can solve your question either in more then one step as written in my last answer or with matrix functions, in your special case (now with columns instead of rows):

{=MAX(IF(A1:F1=1;COL(A1:F1);-1))}

am2
  • 380
  • 5
  • 21