need help: lookup multiple data!

[nguyenthao_89]

Hi alls,
I am facing a difficulty with my analysis excel sheet. I hope you guys can help me solve this problem Thanks alot.
I have an excel sheet as below:
<TABLE style="WIDTH: 223pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=296><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 121pt; mso-width-source: userset; mso-width-alt: 5888" width=161><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><TBODY><TR style="HEIGHT: 32.25pt" height=43><TD style="BORDER-BOTTOM: darkgray 0.5pt solid; BORDER-LEFT: white 0.5pt solid; BACKGROUND-COLOR: #538ed5; WIDTH: 64pt; HEIGHT: 32.25pt; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" class=xl71 height=43 width=85>Employee</TD><TD style="BORDER-BOTTOM: darkgray 0.5pt solid; BORDER-LEFT: white; BACKGROUND-COLOR: #538ed5; WIDTH: 121pt; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" class=xl71 width=161>Project / Cost Centre</TD><TD style="BORDER-BOTTOM: darkgray 0.5pt solid; BORDER-LEFT: white; BACKGROUND-COLOR: #538ed5; WIDTH: 38pt; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" class=xl71 width=50>Sales
Projects hours


</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=21>325 - R</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>110036 - I</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: gray 0.5pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl68></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Total 110036 - I</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl70></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20>325 - R</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>1471028 - M</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl68></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Total 1473454 - A</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl70></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 height=20>Total 325 - R</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20>613 - Gev</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>1471042 - B</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl68>32</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Total 1471042 - B</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl70>32</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20>613 - Gev</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>1471052 - D</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl68>72</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Total 1471052 - D</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl70>72</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20>613 - Gev</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>1473861 - D</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl68>48</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Total 1473861 - D</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl70>48</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 height=20>Total 613 - Ge</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73>152</TD></TR></TBODY></TABLE>

As you can see, this sheet show me the sale project hours per employee and the project they involved in.
And what i need to do is in other sheet, i must give the info:
- sale project hours per project, and
- which employees involved in it, and
- hours per employee spent on it
The outcome would be like this:
<TABLE style="WIDTH: 237pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=315><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5924" width=162><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 height=20 width=64>project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; WIDTH: 67pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=89>employee</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; WIDTH: 122pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=162>sale projects hours</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20>000-A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>123-AB</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>456-CD</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>001-B</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right>13</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>789-EF</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>123-AB</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>111-GH</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 height=20>TOTAL</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>25</TD></TR></TBODY></TABLE>

I have tried using many different lookup formulas but they seems do not work well in this case. Is there any idea arround? It would be much appreciated!
Thanks guys in advance.
Here is my sheet, you can take a look at it:
http://www.4shared.com/file/NOHNlT6h/MrExcel_help.html

Jenny

 

[shadow12345]

not quite sure i understand how these would tie up (project numbers don't seem to appear on the first sheet)

But im guessing you want to return all of the vlookup values for a project and not just the first value it finds? If thats what you need then try searching the forum for MVLOOKUP, that will do it.

 

[nguyenthao_89]

not quite sure i understand how these would tie up (project numbers don't seem to appear on the first sheet)

But im guessing you want to return all of the vlookup values for a project and not just the first value it finds? If thats what you need then try searching the forum for MVLOOKUP, that will do it.

Hi,
The outcome table is just an example what i want :P that's why the project code is not appear in the 1st sheet.
Thanks for ur idea about MVlookup, i'll search for it.

 

[nguyenthao_89]

I have used MVlookup, but it returns #VALUE! error.
Is there any other suggestion while i am not really familiar with VBA and macro
Thanks,