Lookup from Table

[fareedexcel]

Hi,

Please help in retrieving the data from a different table. I tried with index match formula but i feel difficult in getting the answer. Please help. I have attached the sample file.

 

[shrivallabha]

You can use INDEX/MATCH like below. Both formulas need to be array entered i.e. CTRL+SHIFT+ENTER.

For HeadCount in cell M6
=INDEX($G$5:$G$14,MATCH($K6&$L6&M$5,$D$5:$D$14&$E$5:$E$14&$F$5:$F$14,0))

For %Completion in cell Q6
=INDEX($H$5:$H$14,MATCH($K6&$L6&Q$5,$D$5:$D$14&$E$5:$E$14&$F$5:$F$14,0))

Both can then be copied down and across.

 

[bosco_yip]

Another option

1] In M6, copied across to P6 and all copied down :

=SUMIFS($G:$G,$D:$D,$K6,$E:$E,$L6,$F:$F,M$5)

2] In Q6, copied across to T6 and all copied down :

=SUMIFS($H:$H,$D:$D,$K6,$E:$E,$L6,$F:$F,Q$5)

 

[fareedexcel]

Thanks for the help much appreciated