"Sami82" wrote...
This seemed to work, thank you very much. But i was hoping that I
could simplify it further by making a custom function, so that it
would be easy to explain to basic users, it would look something
like this:

=ACNLookup(State,product,date)
....

Without minimal error checking, something like


Function ACNLookup( _
s As string, _
p As String, _
d As Variant _
) As Variant
'----------------------
Dim ws As Worksheet, r As Long, c As Long

On Error Resume Next

Set ws = Application.Caller.Parent.Parent.Worksheets("ACN-" & s)

If Err.Number < 0 Then
ACNLookup = CVErr(xlErrRef) 'bad worksheet, return #REF!
Exit Function
End If

r = Application.Worksheet.Match(p, ws.Range("A4:A30"), 0)
c = Application.Worksheet.Match(p, ws.Range("B3:IV3"), 0)

If Err.Number < 0 Then
ACNLookup = CVErr(xlErrNA) 'bad prod/date, return #N/A
Exit Function
End If

ACNLookup = ws.Range("A3").Offset(r, c).Value
End Function