lookup formula to sum percent

[oxicottin]

Hello, I have been trying to figure out how to do this for two weeks and I'm lost.... Lets start off With my TEST sheet and Employee 1. What I need it to do is if I enter an Act # (1) which is an "Activity Number" into cell A5 and a Date (1/26/18) into B5 I want it to lookup the corresponding data in the Safety Activity "Legend" at the bottom and display the Act % "Activity %" in Employee 1's B25 which would be .4.

Should give Safety Activities Act #.... =VLOOKUP(A5,A30:B36,1,0)
Should gives Safety Activities Act %.... =VLOOKUP(A5,$A$30:$AB$36,28,0)

The problem I have is there is a limit "Activity Limit" to the times a Act # can be performed. So using the example above I used Act # 1 and its Activity Limit is 2 so if an employee enters it twice lets say A5 on 1/26/18 and A6 on 1/27/18 they reached their limit so how do I make it not be able to add an .4 in if someone enters another Act 1 lets say on A7 1/28/18 I want them to get zero percent for that because they reached their limit.

[Paul_Hossler]

I'd make a user defined function since IMHO the logic is too complicated for just worksheet formulas

IF I UNDERSTAND your logic ...

Option Explicit

Function ActivityPercent(actNumbers As Range, ActLimits As Range) As Double
    Dim i As Long
    Dim actNumber As Variant, limitNumber As Variant, limitMax As Variant, limitPercent As Variant
    Dim actCount() As Long
    Dim actTotal As Double
    
    Application.Volatile
    
    'move data into arrays
    With Application.WorksheetFunction
        actNumber = .Transpose(actNumbers.Columns(1))
        limitNumber = .Transpose(ActLimits.Columns(1))
        limitMax = .Transpose(ActLimits.Columns(2))
        limitPercent = .Transpose(ActLimits.Columns(28))
    End With


    'create and init a max count array
    ReDim actCount(LBound(limitNumber) To UBound(limitNumber))
    For i = LBound(limitMax) To UBound(limitMax)
        actCount(i) = limitMax(i)
    Next I

    'see if activity count exceeded by decrementing max
    For i = LBound(actNumber) To UBound(actNumber)
        If actNumber(i) > 0 Then
            If limitMax(actNumber(i)) > 0 Then
                limitMax(actNumber(i)) = limitMax(actNumber(i)) - 1
                actTotal = actTotal + limitPercent(actNumber(i))
            End If
        End If
    Next I

    ActivityPercent = actTotal

End Function

[oxicottin]

Spot on... Thank you!