Advanced Formula Challenge #12: An Array of Matches 81

The challenge this week is as follows: given a range of arbitrary size in which each entry is either 0 or 1 and in which each row contains at least one occurrence of a 1, a single formula to return an array consisting of the relative column positions of the first occurrence of a 1 within each row.

For example, given the below in A1:E10:

An Array of Matches

the solution would be the array:


Readers may reference the range A1:E10 in their solution, though of course being aware that this choice is purely arbitrary and hence that any solution must also hold for a range of any size.

Readers should also note that the entries in the returned array are to be the relative column positions within the range (just as if we’d used MATCH on each of the rows within that range). As such, moving the above range to, for example, H1:L10 would have no impact on the output of any solution.

Also note that this is NOT a shortest formula challenge!

Solution next week. Good luck!


  1. Nice challenge!

    Taking advantage of your binary specifications, and assuming in this case a 10×10 grid (A1:J10):



  2. @Alex

    Can you clarify what results that formula gives when the number of rows in the range is not equal to that for the number of columns?

    Have you got your matrix multiplication correct?

    Perhaps you should stick – as I specified – to referencing the range A1:E10, just so that your answer will be consistent with any others that may be posted.


  3. Hi, XOR LX
    Here’s a my first try.

  4. @XOR LX

    Fixed (and also shamelessly changing TRUNC to INT after aMareis’ posting):

  5. On my mind, aMareis made better solution, because you can refer to named range (for example, binary matrix is in range named “Matrx”). Alex solution is also really cool, but you have to refer to a single row in a range.

    Anyway, both solutions a great! As for me there is too much maths to find appropriate way, specially LOG part πŸ™‚

  6. Hi – I am generally not so good in such challenges, however, below is my immature try…

  7. Another aproach could be:


    I’m looking for another solution. Blessings!

  8. @John Jairo

    Very original! Like it! Interesting use of FIND and TEXT and certainly not an approach which I’d considered.

    Excellent stuff. Cheers!

  9. My approach is similar John Jairo

  10. @sam

    And I’m sure John won’t mind me saying that you’ve improved that set-up considerably.

    Excellent work yet again. Very impressed with all contributions so far to this challenge.


  11. @Maxim

    Based on your feedback I’ve revised mine slightly (making it more similar to aMareis in the process):



  12. Oops I was too quick on the gun, needs to be revised to:


    Not quite as pretty anymore…

  13. @Sam, @JohnJairo

    Fellow, I like your solutions, but I’m confused about the use of the COLUMN(A1:E1) construction in your formula…

    I don’t understand how to make the formula work when I move the data to Columns H:L — as indicated in the original post.

    Perhaps I’m missing something?

  14. @Isaac

    Good point. Thanks.

    Although it would only appear to be Sam’s construction which fails if the range is changed. John Jairo’s seems sound to me. Can you clarify?


  15. @XOR LX – Nice challenge and great ideas presented so far. One issue i have is that in the spec it says “any solution must also hold for a range of any size” so perhaps this could be more strictly adhered to.

    For example the solution of aMareis and Alex with a range containing a row of 47 ones returns zero for that line. Also JJV and sam’s suggestions allow only up to 255 columns.

    I haven’t given much thought to it but it should be possible to overcome some of these limitations by operating on ranges without converting to numbers or strings. I guess you may have had something like that in mind?

    @John Jairo V – your challenge over at Chandoo is a good one – I hope you get some more takers…

  16. @Lori

    Agreed. I confess that I have been quite unrigorous in my checking of solutions presented so far, which does seem somewhat pointless given that, as you say, I laid out quite specific guidelines.

    And you’re also correct when you intimate what I had in mind. The solution I myself derived is not thus restricted. However, I think I was overly-swayed by the ingenuity of some of the posts so far (which in my opinion have showed impressive creativity, if at a cost of rigour), and so “let my guard down” when it came to submitting those solutions to a thorough analysis.

    In fact, as I have done several times in these formula challenges, I again shot myself in the foot somewhat. I had originally intended for this challenge to serve as an opener for the general problem of obtaining an “array of MATCHes”, i.e. given any values in the range. The fact that I then stipulated that this range would consist of only either 0s or 1s did not, at the time, seem like it should make a difference, though of course I had not then envisaged the ingenious solutions of Alex and aMareis. Had I done so, I would most certainly have set it up so that the non-unity entries were random integers (or perhaps, even better, all text).

    Still, sometimes good things come out of such “accidents”, and from the solutions given thus far I have to say that I’m almost glad that I made a slight slip in the designing of this challenge.

    Still, that doesn’t excuse the poor job of checking that I’ve done, so I’ll be sure to improve in that department for the next challenge.


  17. Here’s an another try.


    Thank you.

  18. I also found that aMareis’ will fail for certain combinations of contiguous zeros and contiguous ones. eg 1 zero followed by 46 ones fails, as does 2 zeroes followed by 45 ones. 3 zeros followed by 44 ones works, as does other combinations with increasing leading zeroes.

    Very clever, though.

    To make Sams and JJ’s formulas truly dynamic – so that they could work on a range called Data wherever that range is located – I added -COLUMNS(Data)-1 to the end.

    Well done, all.

  19. Hmmm…. based on solutions by @Sam and @JohnJairo, I got this one:


    Tested on named range, which location on sheet is random. At least up to 100 columns in tested range it works fine for me. Hope I correctly translated formula to English version.

  20. @Maxim

    You say “up to 100 columns“. Are you aware of the precise limitation here? Have you read Lori’s contribution?

    Although I’ve been a bit slack in terms of adhering to the specifications that I laid out, recall that I did say that a solution should work for any range.


  21. @XOR LX, got it. Yes, my decision has limitations, at least on string length and max 10^308.

    Ok, still thinking on this…

  22. aMaris – you need to get rid of that hard-coded 5 in your 10^5 so that the formula handles a range of arbitrary size. Amending your formula accordingly – and using the named range Data – gives this:

  23. Okay, I’ve got one:


    Using the Redimensioning from the last post.

    I think I can do away with the transpose at the end with some effort

  24. Here it is, without the TRANSPOSE and oriented the correct way:

  25. @Jeff wow… Wow!!! Excellent!

    PS you can replace COLUMNS(Data)*ROWS(Data) with COUNT(Data) – a little bit easier… πŸ™‚

  26. Maxim: good point.

  27. Oops… Jeff, test your solution on a range with a number of cells more than 16348. For example, 130×130. It returns an error because columns count exceeds limit on sheet.

  28. @Jeff, would you please? I think it will be best solution for now, regarding array size

  29. Couldn’t reists trying this one.

    This formula is durable to any size range…(If you’ve got the patience to wait for REALLY large ranges to be calculated):


    I tested it on D5:XES132 and it took several seconds to calculate, using this


    Maybe I did something wrong, but I couldn’t get the other posted formulas to work on that range.

  30. @Maxim:

    Here’s my revised formula, which should handle tables with up to 1048576 cells. Not that I’ve tested it!

  31. @Ron: that’s awfully clever. Here’s a generalised version:


    …where Data is a named range pointing at the 2d block.

  32. @Ron: give the range the name Data, then try the following:













    @XOR: Might I suggest that for future challenges you stipulate that a named range called d must be used, and any other ranges must be constructed from that? That way it makes it easy for all to not only test these, but reuse them.

    A sample spreadsheet wouldn’t go amiss either…was a bit of a pain to type out all those ones and zeros to match your screenshot.

  33. Restating the formula I posted, utilizing a referenced range named “Data”:

  34. =AGGREGATE(15,6,COLUMN(Data)*IF(INDEX(Data,ROW(A1),)=0,"",INDEX(Data,ROW(A1),)),1)=COLUMN(A1)

    Data is the name range

  35. @Jeff, thanks!!!

    @Ron, I still do not understand how your solution works, but tested it on 3489366 cells (213 rows and 16382 columns)… and as far as I can see, it works on this really big amount of data. Even if 1 is in last column only. Excellent!

  36. @Ron

    Excellent stuff!

    Ingenious use of LARGE means that this set-up is indeed applicable to very large ranges.

    Thanks a lot.

  37. @chrisham

    Thanks, but I couldn’t get your solution to work. In fact, it appears that it will only ever return a single value, not an array as required.

    Can you clarify?


  38. @Jeff

    Completely agree re your point about Named Ranges – will do so for any future challenges.

    I usually add the attachment, though didn’t feel it was necessary here, since in any case the actual values that I gave were of course arbitrary, and so I presumed that readers would set up their own sheets with some random-generating functions for testing purposes.

    However, I shall endeavour to post the attachment in future, even if I feel it is not necessarily required.


  39. I timed these on 10 columns * 2000 rows = 20,000 cells. Here’s the recalc times:

    Ron Coderre: 24 seconds
    aMaris: 17 seconds
    Jeff: 0.7 seconds
    Sam: 0.014 seconds
    John Jairo V: 0.016 seconds
    Maxim Zelensky: 0.01 seconds
    Alex Groberman: 0.006 seconds

    For some reason though, it took nearly 5 minutes of times from when I array-entered my formula to when it showed the result in the spreadsheet. It didn’t show ‘calculating’ in the status bar during this time. I tried several times: Excel appears to go unresponsive, and then finally returns the numbers. But when I time the ‘entered’ formulas, I get the fast time shown above.


  40. My friend from other forum, where asked for solution of this task, suggested this formula with limit up to 1022 columns:


    Really formula works fine with 16384 columns, but 1022 is a maximum relative position for 1s.

    For a range started in 1st column, he suggested shorter solution with the same limits:

  41. @Maxim

    Then tell your friend to visit this site more often! With elegant constructions such as those it would be nice indeed to have their input on a regular basis.


  42. @Maxim

    I really like that approach. It’s moderately easy to understand and it’s very flexible.

    Here are examples of how I altered it to return the first position of various values for each referenced row.

    First Positive value (if no value is below zero):


    First Positive value (ignoring negative values, if they exist)


    First Non-Zero value


    First occurrence of a particular word (allowing for the word to be missing)

  43. Hmmm…the website didn’t allow the Not-Equal sign in the First Non-Zero value. It simply removed it and left Data0.


  44. @XOR LX, already done! ))

    Hope he will visit your site soon.

    BTW, another man suggested this solution:


    which, being entered CSE in range of cells on sheet, returns correct results. But it is impossible (for me) return as a result an array in any form. How it could be enveloped in other function to get an array? Or this is impossible?

  45. and another one, this time mine πŸ™‚

  46. @Maxim

    Ah no! Of course that multi-cell array solution is disallowed! We must create an array containing the results, not a range of worksheet cells.

    And I do not believe that that set-up can be translated as such.

    P.S. Re your latest offering, is NEXT a new 2013 function that I don’t know about? πŸ™‚


  47. MichaelCH is the author of this fine LOG formula I have posted before. He does not speak English (almost), so he, may be, missing some things.

    Formula he posted is, as far as I can see, for data range started in 1st column, and has a limit of 308 columns. It is shortest formula solution (yes, here it is Advanced challenge, but on that our forum we used to shortest formula challenge, that’s why πŸ™‚ )

    Ah, and yes, NEXT ought to be TEXT – misspelled while translated formula ))

  48. Constructing a general formula from this fruitful discussion with the possible occurrence of errors in the data array.


    Where Data is the array and Check is the comparison.

    Some general comparisons:

    First empty cell: (Data=””)
    First non-empty cell: (Data””)
    First number: ISNUMBER(Data)
    First text: ISTEXT(Data)
    First logical value: ISLOGICAL(Data)
    First error: ISERROR(Data)

  49. This construction from aMareis seems incredible to me:


    I’ve not come across this before. It doesn’t evaluate correctly using either the F9 trick or the Evaluate Formula functionality.

    If I’m understanding it correctly, it is producing multiple 1D arrays on the fly form Data that SUBTOTAL is then processing.

    How the heck does that work? How on earth did you come across it, aMareis?

  50. @Ron: Your formula can be further simplified by ditching a pair of brackets as well as a +1 – i.e. the bits in bold below:


    …which leaves this:


    …although for the sake of explaining it, I find it easier to add this:

  51. @Ron: The outer ROUND seems to be superfluous too.

    This seems to work just fine:

  52. Just for fun…

    Felt simpler with…


    …than where =INT(-1,9) –> -2

  53. Hi Ola.S. Nice formulas. Note that to handle the data being located elsewhere, you’d need to amend them to:



  54. =SMALL(IF(A1:E10=1,COLUMN(A1:E10),COLUMNS(A1:E10)+1)+ROW(A1:E10)*10^10,(ROW(A1:E10)-1)*COLUMNS(A1:E10)+1)-ROW(A1:E10)*10^10

    Try this array formula

  55. I found this challenge a today. I guess I’m late to the party, but this is what I came up with:

  56. Nice Daniel. Needs a slight modification to handle the data being located elsewhere:

  57. Following up on Siva’s suggestion, i think this would work for any size data range:


    or (rounded to nearest integer):


    Excel Hero’s applies to a range with up to 18 columns and the variation of Ola.S and others around 300 columns or so.

  58. Only two years behind!


    where the index k is given as a named formula


    Strictly speaking, this result is not an array because of the use of INDEX. It can be array-output to a range but cannot be aggregated further in memory.

  59. I seem to be wandering round in circles. For the record, just in case the site is still live …


    where ‘test?’ is the Boolean result of any condition one may place on the values held in the range (in this case the [0,1] does the job), ‘p’ is the column index and ‘k’ the row index / record number.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.