Simplify if function

I want to simplify the formula in column E, for small samples it is easy but I need to use it for a really large sample.
The formula is a if function
The formula: look first if the formula above the cell is a zero if this is not the case, look if the cell above that cell is a zero, if that is not the case look if the cell above that cell is a zero and so on. If the value in the cell is an one the answer is the cell right of it.

I have attached a example, the idea is really simple, if you look at the formula in cell b9 you get the idea

I hope someone can help me.

Thanks!

Hi keis386,

I think you want this formula in B4 and pull it down the column B.

=OFFSET(B4,0,1+COUNTIF(B$3:B3,">0"))

Try this workbook

In A4 (Helper Column)

=IF(COUNTIF(C4:S4,0)>0,ROW()+1,"")

In B4

=IF(B3=0,IF(MAX($A$4:$A4)=0,C4,INDEX($A$1:$S4,MAX($A3:$A$4),COUNT(C4:S4))),INDEX($A$1:$S4,ROW(),COUNT(C4:S4)+1))

Drag/Fill Both Down

This should give results up to Column S, extend this in all references as required
e.g.
INDEX($A$1:$S4,ROW(),COUNT(C4:S4)+1)
might become
INDEX($A$1:$X4,ROW(),COUNT(C4:X4)+1)

Hope this helps

I made a lot less complicated, the only problem still is column L for the second and third matrix. I changed already several numbers in the formula, I think the problem lies by the index part. See attachment

I'm still not clear what your end result should be.

Try this workbook

I have cleaned out all formula and cells with spaces rarther than blanks then added some conditional formatting to make the data a bit easier to read.

I have added the following macros

1/. "CleanMatrices" This cleans out all formula and non numeric values from the matrice area.

2/. "ValuesByAlternateRows" This is only for checking purposes.
It changes all numeric values, greater than zero, to sequential numbers, this should make it easier to check if the proper value is returned in Column L

3/. "ValuesByColumn" This is similar to the above, but it changes all numeric values, greater than zero, to the value in the "Header" row 2.

4/. "ReturnValuesToOnes" does as the name suggests, changes all numeric values, greater than zero, back to 1.

Use these macros to visually check if the the values returned by the formula in Column L are correct.

There is a formula in Column A that numbers the rows for each matrix.

Let me know if this is correct, and if not try explaining the result you are expecting on a worksheet with no formula change the "1" to return with something unique, maybe use colours to show the options.

The formulae you originally posted seem to ignore the last value in every row.
Is this correct?