Need help with a formula maybe "Match"

[jcaptchaos2]

Hello,

I have this table of information, I need a formula in cell E3 in the bottom table that would look at the top table and return the pay time for that employee number and that date. I would need a formula for F - I row 3 also but if you could suggest one for E3 I think I could figure the rest out.
<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>I</b></td><td align="center" bgcolor="#C0C0C0"><b>J</b></td><td align="center" bgcolor="#C0C0C0"><b>K</b></td><td align="center" bgcolor="#C0C0C0"><b>L</b></td><td align="center" bgcolor="#C0C0C0"><b>M</b></td><td align="center" bgcolor="#C0C0C0"><b>N</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Emp# </td><td rowspan="1" colspan="1" width="165" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Employee Name </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Date </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Time On </td><td rowspan="1" colspan="1" width="114" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Time Off </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Pay Time </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>3</b></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">8001 </td><td rowspan="1" colspan="1" width="165" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Employee A </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">4/26 </td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">13:57 </td><td rowspan="1" colspan="1" width="114" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">22:39 </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">8:12 </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>4</b></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">8013 </td><td rowspan="1" colspan="1" width="165" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Employee B </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">4/25 </td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">6:00 </td><td rowspan="1" colspan="1" width="114" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">14:42 </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">8:12 </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>5</b></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">8014 </td><td rowspan="1" colspan="1" width="165" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Employee c </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">4/25 </td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">6:00 </td><td rowspan="1" colspan="1" width="114" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">15:42 </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">8:12 </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>6</b></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">8015 </td><td rowspan="1" colspan="1" width="165" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Employee d </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">4/25 </td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">6:00 </td><td rowspan="1" colspan="1" width="114" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">14:00 </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">8:12 </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>7</b></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">8016 </td><td rowspan="1" colspan="1" width="165" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Employee e </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">4/25 </td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">6:00 </td><td rowspan="1" colspan="1" width="114" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">14:42 </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">8:12 </td></tr>
</table>
<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>B</b></td><td align="center" bgcolor="#C0C0C0"><b>C</b></td><td align="center" bgcolor="#C0C0C0"><b>D</b></td><td align="center" bgcolor="#C0C0C0"><b>E</b></td><td align="center" bgcolor="#C0C0C0"><b>F</b></td><td align="center" bgcolor="#C0C0C0"><b>G</b></td><td align="center" bgcolor="#C0C0C0"><b>H</b></td><td align="center" bgcolor="#C0C0C0"><b>I</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF">Emp# </td><td rowspan="1" colspan="1" width="96" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Shift </td><td rowspan="1" colspan="1" width="249" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF">Employee Name </td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF">25-Apr </td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF">26-Apr </td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF">27-Apr </td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF">28-Apr </td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF">29-Apr </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>3</b></td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF">8004 </td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF">1 </td><td rowspan="1" colspan="1" width="249" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF">Employee A </td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="96" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
</table>

 

[Aladin Akyurek]

E3, control+shift+enter, not just enter:

=INDEX($N$:$N$7,MATCH(1,IF($I$3:$I$7=B3,IF($K$3:$K$7=E$2,1)),0))

and copy across.

 

[jcaptchaos2]

Thanks Aladin, I forgot to mention that is on another sheet (sheet1)

 

[jcaptchaos2]

I tried this but am getting #N/A

=INDEX(Sheet1!$N$7,MATCH(1,IF(Sheet1!$I$3:$I$7=B3,IF(Sheet1!$K$3:$K$7=E$2,1)),0))

 

[Aladin Akyurek]

I tried this but am getting #N/A

=INDEX(Sheet1!$N$7,MATCH(1,IF(Sheet1!$I$3:$I$7=B3,IF(Sheet1!$K$3:$K$7=E$2,1)),0))

You need to confirm this formula with pressing the control and shift keys down at the same time, while you hit the enter key.

 

[jcaptchaos2]

Sorry that should have been, it did not seem to like the $N$:$N$7 part of your formula so I changed it to $N$3:$N$7 but get the #N/A

=INDEX(Sheet1!$N$3:$N$7,MATCH(1,IF(Sheet1!$I$3:$I$7=B3,IF(Sheet1!$K$3:$K$7=E$2,1)),0))

 

[jcaptchaos2]

Ok the new code entered fine but still get the #N/A

 

[Akashwani]

Hi,

You need to lock B3 I think,
How about.....

=SUMPRODUCT((Sheet1!$I$3:$I$7=$B$3)*(Sheet1!$K$3:$K$7=E$2),Sheet1!$N$3:$N$7)

Aladin, sorry to jump in on this, just curious as to which is the quicker calculation wise.

Thanks

Ak

 

[jcaptchaos2]

Ak,
Thanks for the suggestion but it says the formula contains an error.

 

[Aladin Akyurek]

Ok the new code entered fine but still get the #N/A

=INDEX(Sheet1!$N$3:$N$7,MATCH(1,IF(Sheet1!$I$3:$I$7=$B3,IF(Sheet1!$K$3:$K$7=E$2,1)),0))

Done the control+shift+enter bit?

Also, the dates must be true dates...