Excel formula to find the value where a row stays zero and return column header

0
votes

I'm trying to create a formula that would be able to find the last zero value in a row by searching a row from right to left. In other words the value where a cell in a row is zero and then all the cells after that are also zero. I've been experimenting with offset, index and lookup but nothing seems to work.

For example, in the data below I would want the formula to come up with the answer "18-Nov-17".

Any suggestions would be greatly appreciated!

here is a sample excel sheet

2
excelexcel-formula

2 Answers

1
votes

Use the following array formula:

=INDEX($P$3:$AI$3,MATCH(2,IF($P$4:$AI$4<>0,1))+1)

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

enter image description here

0
votes

Try this. When it finds the first occurrence of 3 consecutive zeros, it returns the value in the cell above the first zero of the three.

EDIT: I just realized that you weren't looking for a macro solution, but this could return what you're looking for just the same I suppose. 

Sub findthedate()
Dim i As Long

For i = 1 To 20
    If Cells("4", i) = "0" Then
        If Cells("4", i + 1) = "0" Then
            If Cells("4", i + 2) = "0" Then
                MsgBox Cells("3", i)
                Exit For
            End If
        End If
    End If
Next i
End Sub