=INDEX($F$3:$F$22,MATCH(MAX(OFFSET($F$3:$F$22,0,MATCH($A2,Table2[[#Headers],[A]:[U]]))),OFFSET($F$3:$F$22,0,MATCH(A2,Table2[[#Headers],[A]:[U]])),0))
Function gethours(r As Range, r1 As Range, r2 As Range, r3 As Range, r4 As Range)
Application.Volatile
Dim cell As Range, fcol As Long, lcol As Long, frow As Long
Dim vl As Long, cp As Long, np As Long, rng As Range, rng1 As Range, l As Long, k As Long
Dim fr As Long, lr As Long, z As String
fcol = r4.Column
lcol = r4.Columns.Count + fcol - 1
fr = r4.Row
lr = r4.Rows.Count + fr - 1
frow = r3.Row
cp = 99999999
For l = fr To lr
Set rng = Range(Cells(l, fcol).Address & ":" & Cells(l, lcol).Address)
vl = Application.WorksheetFunction.Max(rng)
If Application.WorksheetFunction.CountIf(rng, vl) > 1 Then
For k = fcol To lcol
If Cells(l, k).Value = vl Then
np = Application.WorksheetFunction.VLookup(Cells(frow, k).Value, r1, 2, 0)
If np < cp Then
cp = np
z = Trim(UCase(Cells(frow, k).Value))
End If
End If
Next k
If UCase(Trim(r.Value)) = z Then gethours = gethours + Cells(l, r2.Column).Value
Else
For k = fcol To lcol
If Cells(l, k).Value = vl Then
If UCase(Trim(r.Value)) = Trim(UCase(Cells(frow, k).Value)) Then gethours = gethours + Cells(l, r2.Column).Value
End If
Next k
End If
Next l
End Function
Function gethours(r As Range, r1 As Range, r2 As Range, r3 As Range, r4 As Range)
Dim cell As Range, fcol As Long, lcol As Long, frow As Long
Dim vl As Long, cp As Long, np As Long, rng As Range, rng1 As Range, l As Long, k As Long
Dim fr As Long, lr As Long, z As String
fcol = r4.Column
lcol = r4.Columns.Count + fcol - 1
fr = r4.Row
lr = r4.Rows.Count + fr - 1
frow = r3.Row
For l = fr To lr
cp = 99999999
Set rng = Range(Cells(l, fcol).Address & ":" & Cells(l, lcol).Address)
vl = Application.WorksheetFunction.Max(rng)
If Application.WorksheetFunction.CountIf(rng, vl) > 1 Then
For k = fcol To lcol
If Cells(l, k).Value = vl Then
np = Application.WorksheetFunction.VLookup(Cells(frow, k).Value, r1, 2, 0)
If np < cp Then
cp = np
z = Trim(UCase(Cells(frow, k).Value))
End If
End If
Next k
If UCase(Trim(r.Value)) = z Then gethours = gethours + Cells(l, r2.Column).Value
z = ""
Else
For k = fcol To lcol
If Cells(l, k).Value = vl Then
If UCase(Trim(r.Value)) = Trim(UCase(Cells(frow, k).Value)) Then gethours = gethours + Cells(l, r2.Column).Value
End If
Next k
End If
Next l
End Function
I think I know what you are asking, although your wording is ambiguous; especially regarding "G:AA".
Just for clarity, please can you advise what figure you are expecting to see in cell [ C2 ], & explain how that value was selected?
Thank you.