Vlookup, Match, and Indirect between 2 sheets same workbook

[jadounnaber]

Hello,

I was searching for a solution to my question; however, I'm not experienced with the Match or Indirect formulas in excel and i'm not quite sure how to write the formula. Below is what I am trying to do:

1) I have two sheets, Sheet 1 and Sheet 2

2) cells in sheet 1 will pull values from sheet 2

3) Sheet 1 has 4 columns:
Column A: Employee ID
Column B: November 2012 Hours
Column C: December 2012 Hours
Column D: January 2013 Hours

4) Sheet 2 has 3 columns
Column A: Employee ID
Column B: YYYYMM in the form 201211 or 201212 or 201301
Column c: Hours


5) I want cell B2 in SHeet 1 to vlookup the Employee ID in cell A1 in Sheet 1 from sheet 2. Then pull value of column C from Sheet 2, when Column B from Sheet 2 equals (201211).

6) In the table Sheet 1) the value in red is what I ultimately want excel to do

Below is how both sheets look like:


Sheet 1)
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Employee ID[/TD]
[TD]November 2012 Hours[/TD]
[TD]December 2012 Hours[/TD]
[TD]January 2013 Hours[/TD]
[/TR]
[TR]
[TD]25415[/TD]
[TD]140[/TD]
[TD]150[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]65978[/TD]
[TD]64[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Sheet 2)

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Employee ID[/TD]
[TD]YYYYMM[/TD]
[TD]Hours Worked[/TD]
[/TR]
[TR]
[TD]25415[/TD]
[TD]201211[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]25415[/TD]
[TD]201212[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]25415[/TD]
[TD]201312[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]65978[/TD]
[TD]201211[/TD]
[TD]64[/TD]
[/TR]
[TR]
[TD]65978[/TD]
[TD]201212[/TD]
[TD]80[/TD]
[/TR]
</TBODY>[/TABLE]



Thank you!

 

[Stan_C]

If you change the formatting of Sheet1 headers this will work..

I formatted B1:D1 = custom mmmm yyy

Sheet1

*	A	B	C	D
Employee ID

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:84px;"><col style="width:174.4px;"><col style="width:149.6px;"><col style="width:157.6px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]November 2012[/TD]
[TD="align: right"]December 2012[/TD]
[TD="align: right"]January 2013[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]25415[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]65978[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =SUMPRODUCT(($A2=Sheet2!$A$2:$A$6)*((YEAR(Sheet1!B$1)&MONTH(Sheet1!B$1))=Sheet2!$B$2:$B$6)*Sheet2!$C$2:$C$6) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4