Need help with a formula maybe "Match"

J

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,032
Office Version
  1. 365
Platform
  1. Windows
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>
 
Sort by date Sort by votes
Hi,


Excel Workbook
BCDEFGHI
2Emp#ShiftEmployee Name25-Apr26-Apr27-Apr28-Apr29-Apr
380011Employee A0:008:120:000:000:00
Sheet2


Is that the result you expect?
Perhaps you need to check the date in K!!!!

Ak
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
AK

Sorry I must have entered it wrong but after re entering it I get 0 for an answer which is not correct.
 
Upvote 0
Hi,

I have tested mine and Aladins formula with all the data provided here....


Excel Workbook
IJKLMN
2Emp#Employee NameDateTime OnTime OffPay Time
38001Employee A26-Apr-1113:5722:3908:12
48013Employee B25-Apr-1106:0014:4208:12
58014Employee c25-Apr-1106:0015:4208:12
68015Employee d25-Apr-1106:0014:0008:12
78016Employee e25-Apr-1106:0014:4208:12
Sheet1


And everything works fine. Aladin's formula returns #N/A if there is no match...


Excel Workbook
BCDEFGHI
2Emp#ShiftEmployee Name25-Apr26-Apr27-Apr28-Apr29-Apr
380141Employee A8:120:000:000:000:00
4***8:12#N/A#N/A#N/A#N/A
Sheet2


Have you checked that ALL your dates are correct?
When I copied the data to my sheet the dates in K were based on 2025!!!

Ak
 
Upvote 0
Not sure what I'm doing wrong but still get 0 when using this but I did get Aladins to work.
<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="192" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Emp# </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Shift </font></td><td rowspan="1" colspan="1" width="498" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Employee Name </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40658 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40659 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=F2+1 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=G2+1 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=H2+1 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>3</b></td><td rowspan="1" colspan="1" width="192" height="63" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">8001 </font></td><td rowspan="1" colspan="1" width="192" height="63" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=Table_Payroll_Access[@Shift] </font></td><td rowspan="1" colspan="1" width="498" height="63" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Employee A </font></td><td rowspan="1" colspan="1" width="192" height="63" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=SUMPRODUCT((Sheet1!$I$3:$I$7=$B$3)*(Sheet1!$K$3:$K$7=E$2),Sheet1!$N$3:$N$7) </font></td><td rowspan="1" colspan="1" width="192" height="63" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=SUMPRODUCT((Sheet1!$I$3:$I$7=$B$3)*(Sheet1!$K$3:$K$7=F$2),Sheet1!$N$3:$N$7) </font></td><td rowspan="1" colspan="1" width="192" height="63" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=SUMPRODUCT((Sheet1!$I$3:$I$7=$B$3)*(Sheet1!$K$3:$K$7=G$2),Sheet1!$N$3:$N$7) </font></td><td rowspan="1" colspan="1" width="192" height="63" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=SUMPRODUCT((Sheet1!$I$3:$I$7=$B$3)*(Sheet1!$K$3:$K$7=H$2),Sheet1!$N$3:$N$7) </font></td><td rowspan="1" colspan="1" width="192" height="63" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=SUMPRODUCT((Sheet1!$I$3:$I$7=$B$3)*(Sheet1!$K$3:$K$7=I$2),Sheet1!$N$3:$N$7) </font></td></tr>
</table>
 
Upvote 0
Akashwani said:
Hi,

I have tested mine and Aladins formula with all the data provided here....


Excel Workbook
IJKLMN
2Emp#Employee NameDateTime OnTime OffPay Time
38001Employee A26-Apr-1113:5722:3908:12
48013Employee B25-Apr-1106:0014:4208:12
58014Employee c25-Apr-1106:0015:4208:12
68015Employee d25-Apr-1106:0014:0008:12
78016Employee e25-Apr-1106:0014:4208:12
Sheet1


And everything works fine. Aladin's formula returns #N/A if there is no match...


Excel Workbook
BCDEFGHI
2Emp#ShiftEmployee Name25-Apr26-Apr27-Apr28-Apr29-Apr
380141Employee A8:120:000:000:000:00
4***8:12#N/A#N/A#N/A#N/A
Sheet2


Have you checked that ALL your dates are correct?
When I copied the data to my sheet the dates in K were based on 2025!!!

Ak
Click to expand...

SumProduct is not a retrieval function. Bringing it in for producing a numeric result can be fatal from an audit point of view. In the current situation, 0:00 would look like an adequate result, while in fact it's not, compared with #N/A.
 
Upvote 0
Hi Aladin,

Thanks for that very wise pointer.

Hi, JC.
If you have the formula that Aladin provided working, stick with it, he is the master, I'm just a curious learner :rofl:

Ak
 
Upvote 0
Guy's

Thanks for your help here is my finished formula so the #N/A's don't show up, would there be a reason why I couldn't sum that row?
Code: 
=IF(ISNA(INDEX(Sheet1!$N$3:$N$7,MATCH(1,IF(Sheet1!$I$3:$I$7=$B3,IF(Sheet1!$K$3:$K$7=F$2,1)),0))),"",INDEX(Sheet1!$N$3:$N$7,MATCH(1,IF(Sheet1!$I$3:$I$7=$B3,IF(Sheet1!$K$3:$K$7=F$2,1)),0)))
 
Upvote 0
jcaptchaos2 said:
Guy's

Thanks for your help here is my finished formula so the #N/A's don't show up, would there be a reason why I couldn't sum that row?
Code: 
=IF(ISNA(INDEX(Sheet1!$N$3:$N$7,MATCH(1,IF(Sheet1!$I$3:$I$7=$B3,IF(Sheet1!$K$3:$K$7=F$2,1)),0))),"",INDEX(Sheet1!$N$3:$N$7,MATCH(1,IF(Sheet1!$I$3:$I$7=$B3,IF(Sheet1!$K$3:$K$7=F$2,1)),0)))
Click to expand...

If you are on Excel 2007 or later, you can invoke instead...

=IFERROR(INDEX(Sheet1!$N$3:$N$7,MATCH(1,IF(Sheet1!$I$3:$I$7=$B3,IF(Sheet1!$K$3:$K$7=F$2,1)),0)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
E
  • Solved
If Formula
Replies
1
Views
98
DanteAmor
DanteAmor
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
105
flashgordie
F
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,586
Messages
6,179,719
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top