Lookup Values from three different columns in a table matrix

[prkhan56]

Hello All
I am using Excel 2010 and have the problem as shown in the attached file.

There are 3 Sheets Report, EmpName,Target

Range Names - EmpName, EmpTable,MonthHeader,YearHeader

I am trying to look up for Targets for each Year against each Employee Name entered in Cell B2 in the Report Sheet

I tried the formula which gives the #REF Error

=VLOOKUP($B$2,EmpTable,INDEX(EmpTable,MATCH(A7,MonthHeader,0),MATCH($D$6,YearHeader,0)))

Can anyone guide me with this problem?

Thanks in advance.
Rashid

[JieJenn]

Something like this?

[Haseeb Avarakkan]

Hello Rashid, Try this,

=SUMPRODUCT((Target!$A$2:$A$13=$B$2)*(Target!$B$2:$B$13=B$6)*(Target!$C$1:$N$1=$A7),Target!$C$2:$N$13)

Copy down & across

[prkhan56]

Thank you
Both Solutions are perfect.

Rashid