Thank you very much Rory for all your responses. That introduced me to the capabilities of using LOOKUP, INDEX, MATCH & OFFSET. I was not using these functions as heavily as I used the VLOOKUP.
However your formula
=LOOKUP(2,1/('Pivot-DataSet'!$B$1:$B$1000=E$4)/('Pivot-DataSet'!$G$1:$G$1000=$B$1),INDEX('Pivot-DataSet'!$C$1:$G$1000,0,MATCH($D5,'Pivot-DataSet'!$C$3:$G$3,0)))
which can be stated as below was a bit complex for me to infer
=LOOKUP(2,1/(PivDatSrcCOERange=RepCOEVal)/(PivDatSrcRepDateRange=RepDateVal),INDEX(PivDatSrcAllStatRange,0,MATCH(RepStat,PivDatSrcHeaderRange,0)))
I have written a vba function to do the job. I call the function as
=getStatus(DatSrcDateRange,FunARM,CoeBI,ReportDate,IndxFunc,IndxCoe,IndxOverallStat)
Function getStatus(dataRange As Range, repFun As Range, repCOE As Range, repDate As Range, indxFun As Range, indxCOE As Range, indxStat As Range) As String
Dim xDataRange As Range
Dim xRepDate As String
Dim xRepFun As String
Dim xRepCOE As String
Dim xIndxFun As String
Dim xIndxCOE As String
Dim xIndxStat As String
xIndxStat = indxStat.Value
xIndxFun = indxFun.Value
xIndxCOE = indxCOE.Value
xRepDate = repDate.Value
xRepFun = repFun.Value
xRepCOE = repCOE.Value
For Each xDataRange In dataRange
If xDataRange.Value = xRepDate Then
If (xDataRange.Offset(0, xIndxFun).Value = xRepFun) Then
If (xDataRange.Offset(0, xIndxCOE).Value = xRepCOE) Then
getStatus = xDataRange.Offset(0, xIndxStat).Value
End If
End If
End If
Next xDataRange
End Function
