• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup from Table

fareedexcel

Member
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.
 

Attachments

shrivallabha

Excel Ninja
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

Excel Ninja
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)

64883
 
Top