vlookup not working

tsh1586

New Member
Joined
Jan 8, 2019
Messages
15
I have a workbook that I am trying to return the total from one worksheet tab to another worksheet tab based on the insured name and plan. I have created a formula that returns the plan name based on the criteria so that the plan name matches on both sheets. I have also combined first and last name, then combined the name with the plan on both worksheets so that I can match the name and the plan to return the amount. It is not returning an amount, I either get a zero or a n/a. Help would be greatly appreciated. I am not sure how to get my entire worksheet to attach. What I need to do is match the name and ADP plan name from worksheet ListBill with the name and plan name on the ADP worksheet and return the Total Employee and Employer amount.
formula using =VLOOKUP(P33,ADP!O2:P352,2,FALSE)

Thanks for any help you can provide... If someone can let me know how to attach the worksheet I will do so. I tried everything I knew to do and it still would not show up.
Tammy
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
A general comment: matching by name / plan name is problematic - is there any unique ID(s) common in both files that could be used?
 

tsh1586

New Member
Joined
Jan 8, 2019
Messages
15
Excel 2013/2016
ABCDEFGHIJKLMNOPQ
32 Insured Last Name Insured First Name Insured ID Division Department Class Coverage Type Coverage Tier Premium Volume Total Premium Combine Name CombineADP Plan NameCombine name and plan Total ADP
33 BAHNWEG NICHOLAS J $ 1.00 IT DIV $ 1.00 AD&D Member only $ 1.40 $ 50,000.00 BAHNWEG,NICHOLAS J AD&DMember only AD&DBAHNWEG,NICHOLAS J-AD&D#N/A
34 BAHNWEG NICHOLAS J $ 1.00 IT DIV $ 1.00 LTD Member only $ 25.10 $ 4,303.00 BAHNWEG,NICHOLAS J LTDMember only Long Term DisabilityBAHNWEG,NICHOLAS J-Long Term Disability#N/A
35 BAHNWEG NICHOLAS J $ 1.00 IT DIV $ 1.00 LIFE Member only $ 9.10 $ 50,000.00 BAHNWEG,NICHOLAS J LIFEMember only Basic LifeBAHNWEG,NICHOLAS J-Basic Life#N/A
36 BAHNWEG NICHOLAS J $ 1.00 IT DIV $ 1.00 STD Member only $ 34.76 $ 993.00 $ 70.36 BAHNWEG,NICHOLAS J STDMember only STDBAHNWEG,NICHOLAS J-STD#N/A
37 BAILEY BRYAN K $ 1.00 POLICE $ 1.00 AD&D Member only $ 1.40 $ 50,000.00 BAILEY,BRYAN K AD&DMember only AD&DBAILEY,BRYAN K-AD&D#N/A
38 BAILEY BRYAN K $ 1.00 POLICE $ 1.00 LTD Member only $ 13.25 $ 2,271.00 BAILEY,BRYAN K LTDMember only Long Term DisabilityBAILEY,BRYAN K-Long Term Disability#N/A
39 BAILEY BRYAN K $ 1.00 POLICE $ 1.00 LIFE Member only $ 9.10 $ 50,000.00 BAILEY,BRYAN K LIFEMember only Basic LifeBAILEY,BRYAN K-Basic Life#N/A
40 BAILEY BRYAN K $ 1.00 POLICE $ 1.00 STD Member only $ 18.38 $ 525.00 $ 42.13 BAILEY,BRYAN K STDMember only STDBAILEY,BRYAN K-STD#N/A
41 BEESTING ALEXANDRA V $ 1.00 PLAN DEP $ 1.00 AD&D Member only $ 1.40 $ 50,000.00 BEESTING,ALEXANDRA V AD&DMember only AD&DBEESTING,ALEXANDRA V-AD&D#N/A
42 BEESTING ALEXANDRA V $ 1.00 PLAN DEP $ 1.00 LTD Member only $ 20.52 $ 3,518.00 BEESTING,ALEXANDRA V LTDMember only Long Term DisabilityBEESTING,ALEXANDRA V-Long Term Disability#N/A
43 BEESTING ALEXANDRA V $ 1.00 PLAN DEP $ 1.00 LIFE Member only $ 9.10 $ 50,000.00 BEESTING,ALEXANDRA V LIFEMember only Basic LifeBEESTING,ALEXANDRA V-Basic Life#N/A
44 BEESTING ALEXANDRA V $ 1.00 PLAN DEP $ 1.00 STD Member only $ 28.42 $ 812.00 $ 59.44 BEESTING,ALEXANDRA V STDMember only STDBEESTING,ALEXANDRA V-STD#N/A
45 BOLICK JOSHUA L $ 1.00 POLICE $ 1.00 AD&D Member only $ 1.40 $ 50,000.00 BOLICK,JOSHUA L AD&DMember only AD&DBOLICK,JOSHUA L-AD&D#N/A
46 BOLICK JOSHUA L $ 1.00 POLICE $ 1.00 DEOAD Children Only $ 0.54 $ 10,000.00 BOLICK,JOSHUA L DEOADChildren Only Child AD&DBOLICK,JOSHUA L-Child AD&D#N/A
47 BOLICK JOSHUA L $ 1.00 POLICE $ 1.00 DEOAD Spouse Only $ 0.29 $ 10,000.00 BOLICK,JOSHUA L DEOADSpouse Only Spouse AD&DBOLICK,JOSHUA L-Spouse AD&D#N/A
48 BOLICK JOSHUA L $ 1.00 POLICE $ 1.00 LTD Member only $ 11.38 $ 1,950.00 BOLICK,JOSHUA L LTDMember only Long Term DisabilityBOLICK,JOSHUA L-Long Term Disability#N/A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
ListBill

Worksheet Formulas
CellFormula
M33=A33&","&B33
N33=G33&H33
O33=VLOOKUP(N33,Vlookup!$C$2:$D$14,2,FALSE)
P33=M33&"-"&O33
Q33=VLOOKUP(P33,ADP!O2:P352,2,FALSE)
M34=A34&","&B34
N34=G34&H34
O34=VLOOKUP(N34,Vlookup!$C$2:$D$14,2,FALSE)
P34=M34&"-"&O34
Q34=VLOOKUP(P34,ADP!O3:P353,2,FALSE)
M35=A35&","&B35
N35=G35&H35
O35=VLOOKUP(N35,Vlookup!$C$2:$D$14,2,FALSE)
P35=M35&"-"&O35
Q35=VLOOKUP(P35,ADP!O4:P354,2,FALSE)
M36=A36&","&B36
N36=G36&H36
O36=VLOOKUP(N36,Vlookup!$C$2:$D$14,2,FALSE)
P36=M36&"-"&O36
Q36=VLOOKUP(P36,ADP!O5:P355,2,FALSE)
M37=A37&","&B37
N37=G37&H37
O37=VLOOKUP(N37,Vlookup!$C$2:$D$14,2,FALSE)
P37=M37&"-"&O37
Q37=VLOOKUP(P37,ADP!O6:P356,2,FALSE)
M38=A38&","&B38
N38=G38&H38
O38=VLOOKUP(N38,Vlookup!$C$2:$D$14,2,FALSE)
P38=M38&"-"&O38
Q38=VLOOKUP(P38,ADP!O7:P357,2,FALSE)
M39=A39&","&B39
N39=G39&H39
O39=VLOOKUP(N39,Vlookup!$C$2:$D$14,2,FALSE)
P39=M39&"-"&O39
Q39=VLOOKUP(P39,ADP!O8:P358,2,FALSE)
M40=A40&","&B40
N40=G40&H40
O40=VLOOKUP(N40,Vlookup!$C$2:$D$14,2,FALSE)
P40=M40&"-"&O40
Q40=VLOOKUP(P40,ADP!O9:P359,2,FALSE)
M41=A41&","&B41
N41=G41&H41
O41=VLOOKUP(N41,Vlookup!$C$2:$D$14,2,FALSE)
P41=M41&"-"&O41
Q41=VLOOKUP(P41,ADP!O10:P360,2,FALSE)
M42=A42&","&B42
N42=G42&H42
O42=VLOOKUP(N42,Vlookup!$C$2:$D$14,2,FALSE)
P42=M42&"-"&O42
Q42=VLOOKUP(P42,ADP!O11:P361,2,FALSE)
M43=A43&","&B43
N43=G43&H43
O43=VLOOKUP(N43,Vlookup!$C$2:$D$14,2,FALSE)
P43=M43&"-"&O43
Q43=VLOOKUP(P43,ADP!O12:P362,2,FALSE)
M44=A44&","&B44
N44=G44&H44
O44=VLOOKUP(N44,Vlookup!$C$2:$D$14,2,FALSE)
P44=M44&"-"&O44
Q44=VLOOKUP(P44,ADP!O13:P363,2,FALSE)
M45=A45&","&B45
N45=G45&H45
O45=VLOOKUP(N45,Vlookup!$C$2:$D$14,2,FALSE)
P45=M45&"-"&O45
Q45=VLOOKUP(P45,ADP!O14:P364,2,FALSE)
M46=A46&","&B46
N46=G46&H46
O46=VLOOKUP(N46,Vlookup!$C$2:$D$14,2,FALSE)
P46=M46&"-"&O46
Q46=VLOOKUP(P46,ADP!O15:P365,2,FALSE)
M47=A47&","&B47
N47=G47&H47
O47=VLOOKUP(N47,Vlookup!$C$2:$D$14,2,FALSE)
P47=M47&"-"&O47
Q47=VLOOKUP(P47,ADP!O16:P366,2,FALSE)
M48=A48&","&B48
N48=G48&H48
O48=VLOOKUP(N48,Vlookup!$C$2:$D$14,2,FALSE)
P48=M48&"-"&O48
Q48=VLOOKUP(P48,ADP!O17:P367,2,FALSE)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

tsh1586

New Member
Joined
Jan 8, 2019
Messages
15
<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">NAME</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">EMPLOYEE STATUS</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">T</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">PLAN TYPE</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">PROVIDER</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">PLAN NAME</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">ELIGIBILITY GROUP</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">PLAN START DATE</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">COVERAGE LEVEL VALUE</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">COVERAGE LEVEL AMOUNT</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">EMPLOYEE COST</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">EMPLOYEE COST PERIOD</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">EMPLOYER COST</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">EMPLOYER COST PERIOD</td><td style="background-color: #FFFFFF;;">Combine Name and plan</td><td style="background-color: #FFFFFF;;">TOTAL EMPLOYEE AND EMPLOYER</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;;">BAHNWEG,NICHOLAS J</td><td style="text-align: center;border-top: 1px solid black;;">Active</td><td style="border-top: 1px solid black;;">IT Division</td><td style="border-top: 1px solid black;;">AD&D</td><td style="border-top: 1px solid black;;">Metropolitan Life Ins Co</td><td style="border-top: 1px solid black;;">AD&D </td><td style="border-top: 1px solid black;;">Enrolled</td><td style="border-top: 1px solid black;;">04/01/2018</td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;">$50,000.00</td><td style="border-top: 1px solid black;;">$0.00 </td><td style="border-top: 1px solid black;;">Monthly</td><td style="border-top: 1px solid black;;">$1.40 </td><td style="border-top: 1px solid black;;">Monthly</td><td style=";">BAHNWEG,NICHOLAS J-AD&D </td><td style="text-align: right;background-color: #D9D9D9;;">1.40 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">BAHNWEG,NICHOLAS J</td><td style="text-align: center;;">Active</td><td style=";">IT Division</td><td style=";">Employee Life</td><td style=";">Metropolitan Life Ins Co</td><td style=";">Basic Life </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";"></td><td style=";">$50,000.00</td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$8.60 </td><td style=";">Monthly</td><td style=";">BAHNWEG,NICHOLAS J-Basic Life </td><td style="text-align: right;background-color: #D9D9D9;;">8.60 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">BAHNWEG,NICHOLAS J</td><td style="text-align: center;;">Active</td><td style=";">IT Division</td><td style=";">Long Term Disability</td><td style=";">Metropolitan Life Ins Co</td><td style=";">Long Term Disability </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";">60% of earnings up to $7000 Per Month</td><td style=";"></td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$25.10 </td><td style=";">Monthly</td><td style=";">BAHNWEG,NICHOLAS J-Long Term Disability </td><td style="text-align: right;background-color: #D9D9D9;;">25.10 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">BAHNWEG,NICHOLAS J</td><td style="text-align: center;;">Active</td><td style=";">IT Division</td><td style=";">Short Term Disability</td><td style=";">Metropolitan Life Ins Co</td><td style=";">STD </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";">60% of earnings up to $1500 Per Week</td><td style=";"></td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$34.75 </td><td style=";">Monthly</td><td style=";">BAHNWEG,NICHOLAS J-STD </td><td style="text-align: right;background-color: #D9D9D9;;">34.75 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Bailey, Bryan</td><td style="text-align: center;;">Active</td><td style=";">Police</td><td style=";">AD&D</td><td style=";">Metropolitan Life Ins Co</td><td style=";">AD&D </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";"></td><td style=";">$50,000.00</td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$1.40 </td><td style=";">Monthly</td><td style=";">Bailey, Bryan-AD&D </td><td style="text-align: right;background-color: #D9D9D9;;">1.40 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Bailey, Bryan</td><td style="text-align: center;;">Active</td><td style=";">Police</td><td style=";">Employee Life</td><td style=";">Metropolitan Life Ins Co</td><td style=";">Basic Life </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";"></td><td style=";">$50,000.00</td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$8.60 </td><td style=";">Monthly</td><td style=";">Bailey, Bryan-Basic Life </td><td style="text-align: right;background-color: #D9D9D9;;">8.60 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Bailey, Bryan</td><td style="text-align: center;;">Active</td><td style=";">Police</td><td style=";">Long Term Disability</td><td style=";">Metropolitan Life Ins Co</td><td style=";">Long Term Disability </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";">60% of earnings up to $7000 Per Month</td><td style=";"></td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$13.25 </td><td style=";">Monthly</td><td style=";">Bailey, Bryan-Long Term Disability </td><td style="text-align: right;background-color: #D9D9D9;;">13.25 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Bailey, Bryan</td><td style="text-align: center;;">Active</td><td style=";">Police</td><td style=";">Short Term Disability</td><td style=";">Metropolitan Life Ins Co</td><td style=";">STD </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";">60% of earnings up to $1500 Per Week</td><td style=";"></td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$18.34 </td><td style=";">Monthly</td><td style=";">Bailey, Bryan-STD </td><td style="text-align: right;background-color: #D9D9D9;;">18.34 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Beesting, Alexandra</td><td style="text-align: center;;">Active</td><td style=";">Planning Department</td><td style=";">AD&D</td><td style=";">Metropolitan Life Ins Co</td><td style=";">AD&D </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";"></td><td style=";">$50,000.00</td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$1.40 </td><td style=";">Monthly</td><td style=";">Beesting, Alexandra-AD&D </td><td style="text-align: right;background-color: #D9D9D9;;">1.40 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Beesting, Alexandra</td><td style="text-align: center;;">Active</td><td style=";">Planning Department</td><td style=";">Employee Life</td><td style=";">Metropolitan Life Ins Co</td><td style=";">Basic Life </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";"></td><td style=";">$50,000.00</td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$8.60 </td><td style=";">Monthly</td><td style=";">Beesting, Alexandra-Basic Life </td><td style="text-align: right;background-color: #D9D9D9;;">8.60 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Beesting, Alexandra</td><td style="text-align: center;;">Active</td><td style=";">Planning Department</td><td style=";">Long Term Disability</td><td style=";">Metropolitan Life Ins Co</td><td style=";">Long Term Disability </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";">60% of earnings up to $7000 Per Month</td><td style=";"></td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$21.55 </td><td style=";">Monthly</td><td style=";">Beesting, Alexandra-Long Term Disability </td><td style="text-align: right;background-color: #D9D9D9;;">21.55 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Beesting, Alexandra</td><td style="text-align: center;;">Active</td><td style=";">Planning Department</td><td style=";">Short Term Disability</td><td style=";">Metropolitan Life Ins Co</td><td style=";">STD </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";">60% of earnings up to $1500 Per Week</td><td style=";"></td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$29.83 </td><td style=";">Monthly</td><td style=";">Beesting, Alexandra-STD </td><td style="text-align: right;background-color: #D9D9D9;;">29.83 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Bolick, Joshua L</td><td style="text-align: center;;">Active</td><td style=";">Police</td><td style=";">AD&D</td><td style=";">Metropolitan Life Ins Co</td><td style=";">AD&D </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";"></td><td style=";">$50,000.00</td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$1.40 </td><td style=";">Monthly</td><td style=";">Bolick, Joshua L-AD&D </td><td style="text-align: right;background-color: #D9D9D9;;">1.40 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Bolick, Joshua L</td><td style="text-align: center;;">Active</td><td style=";">Police</td><td style=";">Employee Life</td><td style=";">Metropolitan Life Ins Co</td><td style=";">Basic Life </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";"></td><td style=";">$50,000.00</td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$8.60 </td><td style=";">Monthly</td><td style=";">Bolick, Joshua L-Basic Life </td><td style="text-align: right;background-color: #D9D9D9;;">8.60 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Bolick, Joshua L</td><td style="text-align: center;;">Active</td><td style=";">Police</td><td style=";">Child AD&D</td><td style=";">Metropolitan Life Ins Co</td><td style=";">Child AD&D  </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";"></td><td style=";">$10,000.00</td><td style=";">$2.90 </td><td style=";">Monthly</td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">Bolick, Joshua L-Child AD&D  </td><td style="text-align: right;background-color: #D9D9D9;;">2.90 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Bolick, Joshua L</td><td style="text-align: center;;">Active</td><td style=";">Police</td><td style=";">Child Life</td><td style=";">Metropolitan Life Ins Co</td><td style=";">Child Life</td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";"></td><td style=";">$10,000.00</td><td style=";">$1.60 </td><td style=";">Monthly</td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">Bolick, Joshua L-Child Life</td><td style="text-align: right;background-color: #D9D9D9;;">1.60 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Bolick, Joshua L</td><td style="text-align: center;;">Active</td><td style=";">Police</td><td style=";">Long Term Disability</td><td style=";">Metropolitan Life Ins Co</td><td style=";">Long Term Disability </td><td style=";">Enrolled</td><td style=";">04/01/2018</td><td style=";">60% of earnings up to $7000 Per Month</td><td style=";"></td><td style=";">$0.00 </td><td style=";">Monthly</td><td style=";">$11.94 </td><td style=";">Monthly</td><td style=";">Bolick, Joshua L-Long Term Disability </td><td style="text-align: right;background-color: #D9D9D9;;">11.94 </td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">ADP</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O2</th><td style="text-align:left">=A2&"-"&F2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P2</th><td style="text-align:left">=M2+K2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O3</th><td style="text-align:left">=A3&"-"&F3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P3</th><td style="text-align:left">=M3+K3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O4</th><td style="text-align:left">=A4&"-"&F4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P4</th><td style="text-align:left">=M4+K4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O5</th><td style="text-align:left">=A5&"-"&F5</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P5</th><td style="text-align:left">=M5+K5</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O6</th><td style="text-align:left">=A6&"-"&F6</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P6</th><td style="text-align:left">=M6+K6</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O7</th><td style="text-align:left">=A7&"-"&F7</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P7</th><td style="text-align:left">=M7+K7</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O8</th><td style="text-align:left">=A8&"-"&F8</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P8</th><td style="text-align:left">=M8+K8</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O9</th><td style="text-align:left">=A9&"-"&F9</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P9</th><td style="text-align:left">=M9+K9</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O10</th><td style="text-align:left">=A10&"-"&F10</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P10</th><td style="text-align:left">=M10+K10</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O11</th><td style="text-align:left">=A11&"-"&F11</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P11</th><td style="text-align:left">=M11+K11</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O12</th><td style="text-align:left">=A12&"-"&F12</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P12</th><td style="text-align:left">=M12+K12</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O13</th><td style="text-align:left">=A13&"-"&F13</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P13</th><td style="text-align:left">=M13+K13</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O14</th><td style="text-align:left">=A14&"-"&F14</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P14</th><td style="text-align:left">=M14+K14</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O15</th><td style="text-align:left">=A15&"-"&F15</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P15</th><td style="text-align:left">=M15+K15</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O16</th><td style="text-align:left">=A16&"-"&F16</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P16</th><td style="text-align:left">=M16+K16</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O17</th><td style="text-align:left">=A17&"-"&F17</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P17</th><td style="text-align:left">=M17+K17</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O18</th><td style="text-align:left">=A18&"-"&F18</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P18</th><td style="text-align:left">=M18+K18</td></tr></tbody></table></td></tr></table><br />
 

tsh1586

New Member
Joined
Jan 8, 2019
Messages
15
that is the only criteria that I have that is unique. I created the name and plan combined to create a unique combo.
 

tsh1586

New Member
Joined
Jan 8, 2019
Messages
15
thank you posted sample data
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,802
Office Version
365
Platform
Windows
The formula in O33 should be
=VLOOKUP(P33,ADP!O$2:P$352,2,FALSE)
and copied down.
But that will not fully work as there are large discrepancies between your two data sets
 

tsh1586

New Member
Joined
Jan 8, 2019
Messages
15
The formula in O33 should be
=VLOOKUP(P33,ADP!O$2:P$352,2,FALSE)
and copied down.
But that will not fully work as there are large discrepancies between your two data sets

Corrected the formula but what discrepancies are you seeing?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,802
Office Version
365
Platform
Windows
Things like, you are looking up the value of
BAILEY,BRYAN K-Basic Life
but on the ADP sheet it's
Bailey, Bryan-Basic Life
 

Watch MrExcel Video

Forum statistics

Threads
1,099,274
Messages
5,467,698
Members
406,549
Latest member
midcoastchris04

This Week's Hot Topics

Top