I want to scan through an array with say 10 columns and 17 rows which hold 0 in most of the cells.

I need to scan from top to bottom row by row and want to highlight the index(in row 1 as 5) at L3 location on the sheet for the first appearance of number 1.

You may see that at first the value 1 appears at E10.

I am unable to understand which excel formula to put to get the value of 5(index in row 1 corresponding to column E10) for having detected 1 for the first time in column E which should print value 5 at L3.

enter image description here

  • If the only values in your range are 0 or 1, you can just sum the range: =SUM(A3:J20) – BillDOe Mar 10 '16 at 23:45
  • Dear BillOer, I am not looking for the summation, but want to identify the first instance of appearance of "1" while scanning top to bottom scanning all rows from top to bottom in the matrix, And when first instance is found out, need to identify the index on the top row of the sheet (which is an identifier and not a part of matrix. Thisis the reason when first 1 is appearing at E10, we want to print value of E1 at the L3 location. – Pankaj Kumar Mar 11 '16 at 1:31

I'm not sure if this will work for you, but you could use the formula

in L3 and then drag down to L20. The result will show in what column the first occurrence of the search string is found. Here's an example:

enter image description here

If this rather clumsy solution doesn't work, I'm thinking it'll take a user defined function to get what you want. It wouldn't be too difficult, just a couple of for/next loops. I could probably write it, but as I don't do code all the time, it may take me a short while.


I found a solution to your problem. Use the following formula in L3:



  • A1:J1 is the lookup range
  • 1 is the lookup row
  • SMALL(IF(A3:J20=1,ROW(A3:J20),5000);1) Finds all rows that contain a 1 and then return the smallest (I added the 5000 to be sure that false option of if definitely isn't the smallest number in this array)
  • The INDIRECT function is then used to build a "row" through which MATCH can search for the right number to use in INDEX

You can always ask additional questions, if my answer isn't clear.

  • Shouldn't those be commas instead of semicolons? – BillDOe Mar 11 '16 at 23:47
  • Yeah sorry I'm using the Dutch version of excel and there semicolons are used as seperators. I just edited my answer, so now it should be alright. – Michthan Mar 12 '16 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.