Index and match help

[cocks17]

I am trying to figure out how I can look up when some activity stopped and then report back on the date it stopped. I think I need to use index and match but I could really use some help. My data and ideal output table is attached in an image to this post. I'd like to only have activity which has stopped to appear, so if there has been activity in the last 2 days then this is considered active.

Please let me know your thoguhts on a formula for the table or if a seperate sheet may need to be created?

Cheers
Paul

 

[Aladin Akyurek]

I am trying to figure out how I can look up when some activity stopped and then report back on the date it stopped. I think I need to use index and match but I could really use some help. My data and ideal output table is attached in an image to this post. I'd like to only have activity which has stopped to appear, so if there has been activity in the last 2 days then this is considered active.

Please let me know your thoguhts on a formula for the table or if a seperate sheet may need to be created?

Cheers
Paul

L4: Activity 1
L5: Activity 2
L6: Activity 3

M4:

=IF(COUNT(INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0)>=2,
     LOOKUP(9.99999999999999E+307,INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0),
       $C$4:$J$4),"")

Is this what you meant?

 

[cocks17]

Hey the formula you gave me seems to be missing perenthesis in places excel tells me. I think I corrected that but it just returns blank cells

=IF(COUNT(INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0)>=2,LOOKUP(9.99999999999999E+307,INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0),$C$4:$J$4),"")

So i think what you typed is right so if the cell has had no value for 2 days then it returns the date it stopped. So for M5 it will say "12/5/2011"

Is this what your formula should achieve?

 

[Aladin Akyurek]

Hey the formula you gave me seems to be missing perenthesis in places excel tells me. I think I corrected that but it just returns blank cells

=IF(COUNT(INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0)>=2,LOOKUP(9.99999999999999E+307,INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0),$C$4:$J$4),"")

So i think what you typed is right so if the cell has had no value for 2 days then it returns the date it stopped. So for M5 it will say "12/5/2011"

Is this what your formula should achieve?

=IF(COUNT(INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0))>=2,
     LOOKUP(9.99999999999999E+307,
        INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0),
        $C$4:$J$4),
     "")

It should return a header date correponding to the last "active" date.

 

[cocks17]

Hey

This is awesome. Thanks for your help. It worked this time.

I need to take this to the next step now. I need to incorporate another IF into it to say that if the result of that formula is equal to today, taday-1 and today-2 then return a blank value?

 

[cocks17]

Hey

This is awesome. Thanks for your help. It worked this time.

I need to take this to the next step now. I need to incorporate another IF into it to say that if the result of that formula is equal to today, taday-1 and today-2 then return a blank value?

For anyone knowlege. To achieve this the formula below worked for me

It takes 3 days of no activity to warrant the activity as stopped and then shows a date. Otherwise shows blank. This allows me to filter the table to not show "blanks" allowing me to only show stopped activity.


=IF(IF(COUNT(INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0))>=2,LOOKUP(9.99999999999999E+307,INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0),$C$4:$J$4),"")=TODAY(),"",IF(IF(COUNT(INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0))>=2,LOOKUP(9.99999999999999E+307,INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0),$C$4:$J$4),"")=TODAY()-1,"",IF(IF(COUNT(INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0))>=2,LOOKUP(9.99999999999999E+307,INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0),$C$4:$J$4),"")=TODAY()-2,"",IF(COUNT(INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0))>=2,LOOKUP(9.99999999999999E+307,INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0),$C$4:$J$4),""))))

 

[Aladin Akyurek]

For anyone knowlege. To achieve this the formula below worked for me

It takes 3 days of no activity to warrant the activity as stopped and then shows a date. Otherwise shows blank. This allows me to filter the table to not show "blanks" allowing me to only show stopped activity.


=IF(IF(COUNT(INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0))>=2,LOOKUP(9.99999999999999E+307,INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0),$C$4:$J$4),"")=TODAY(),"",IF(IF(COUNT(INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0))>=2,LOOKUP(9.99999999999999E+307,INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0),$C$4:$J$4),"")=TODAY()-1,"",IF(IF(COUNT(INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0))>=2,LOOKUP(9.99999999999999E+307,INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0),$C$4:$J$4),"")=TODAY()-2,"",IF(COUNT(INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0))>=2,LOOKUP(9.99999999999999E+307,INDEX($C$5:$J$7,MATCH(L5,$B$5:$B$7,0),0),$C$4:$J$4),""))))

Holy cow...

If you add the following code to your workbook as a module:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

We can invoke:

=IF(COUNT(INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0))>=2,
    IF(V(LOOKUP(9.99999999999999E+307,
        INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0),
        $C$4:$J$4))>=TODAY()-2,"",V()),
     "")

 

[cocks17]

Holy cow...

If you add the following code to your workbook as a module:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

We can invoke:

=IF(COUNT(INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0))>=2,
    IF(V(LOOKUP(9.99999999999999E+307,
        INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0),
        $C$4:$J$4))>=TODAY()-2,"",V()),
     "")

Thank you! Were really cooking now on this one! incredible! VBA module worked a treat! So one last ask on this. Say for instance we want to add another dimension to this and we only want to show this month and if its not this month then its blank?

 

[markmzz]

****s17,

If I understand what you are intending to, the following formula (array formula - press Ctrl+Shift+Enter and not only Enter) would be a second option:

=IF(SUM((L4=$B$5:$B$7)*(OFFSET(INDEX($C$5:$J$7,,MATCH(TODAY(),$C$4:$J$4)),0;-3,,3)="")),INDEX($C$4:$J$4,COUNT($C5:$J5)),"")

Markmzz

 

[cocks17]

****s17,

If I understand what you are intending to, the following formula (array formula - press Ctrl+Shift+Enter and not only Enter) would be a second option:

=IF(SUM((L4=$B$5:$B$7)*(OFFSET(INDEX($C$5:$J$7,,MATCH(TODAY(),$C$4:$J$4)),0;-3,,3)="")),INDEX($C$4:$J$4,COUNT($C5:$J5)),"")

Markmzz

Is it possible to make this recognise weather it is this months only? as the date line can be a lot longer than my example above.

Thanks
Paul