vlookup not working

[tsh1586]

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

 

[Fluff]

You cannot upload files to the site,but there are some add-ins available here that will enable you to add some sample data to a post. https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[GR00007]

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]

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
32	Insured Last Name	Insured First Name	Insured ID	Division	Department	Class	Coverage Type	Coverage Tier	Premium	Volume	Total Premium		Combine Name	Combine	ADP Plan Name	Combine 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&D	BAHNWEG,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 Disability	BAHNWEG,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 Life	BAHNWEG,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	STD	BAHNWEG,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&D	BAILEY,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 Disability	BAILEY,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 Life	BAILEY,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	STD	BAILEY,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&D	BEESTING,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 Disability	BEESTING,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 Life	BEESTING,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	STD	BEESTING,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&D	BOLICK,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&D	BOLICK,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&D	BOLICK,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 Disability	BOLICK,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 Cell Formula 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]

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	NAME	EMPLOYEE STATUS	T	PLAN TYPE	PROVIDER	PLAN NAME	ELIGIBILITY GROUP	PLAN START DATE	COVERAGE LEVEL VALUE	COVERAGE LEVEL AMOUNT	EMPLOYEE COST	EMPLOYEE COST PERIOD	EMPLOYER COST	EMPLOYER COST PERIOD	Combine Name and plan	TOTAL EMPLOYEE AND EMPLOYER
2	BAHNWEG,NICHOLAS J	Active	IT Division	AD&D	Metropolitan Life Ins Co	AD&D	Enrolled	04/01/2018		$50,000.00	$0.00	Monthly	$1.40	Monthly	BAHNWEG,NICHOLAS J-AD&D	1.40
3	BAHNWEG,NICHOLAS J	Active	IT Division	Employee Life	Metropolitan Life Ins Co	Basic Life	Enrolled	04/01/2018		$50,000.00	$0.00	Monthly	$8.60	Monthly	BAHNWEG,NICHOLAS J-Basic Life	8.60
4	BAHNWEG,NICHOLAS J	Active	IT Division	Long Term Disability	Metropolitan Life Ins Co	Long Term Disability	Enrolled	04/01/2018	60% of earnings up to $7000 Per Month		$0.00	Monthly	$25.10	Monthly	BAHNWEG,NICHOLAS J-Long Term Disability	25.10
5	BAHNWEG,NICHOLAS J	Active	IT Division	Short Term Disability	Metropolitan Life Ins Co	STD	Enrolled	04/01/2018	60% of earnings up to $1500 Per Week		$0.00	Monthly	$34.75	Monthly	BAHNWEG,NICHOLAS J-STD	34.75
6	Bailey, Bryan	Active	Police	AD&D	Metropolitan Life Ins Co	AD&D	Enrolled	04/01/2018		$50,000.00	$0.00	Monthly	$1.40	Monthly	Bailey, Bryan-AD&D	1.40
7	Bailey, Bryan	Active	Police	Employee Life	Metropolitan Life Ins Co	Basic Life	Enrolled	04/01/2018		$50,000.00	$0.00	Monthly	$8.60	Monthly	Bailey, Bryan-Basic Life	8.60
8	Bailey, Bryan	Active	Police	Long Term Disability	Metropolitan Life Ins Co	Long Term Disability	Enrolled	04/01/2018	60% of earnings up to $7000 Per Month		$0.00	Monthly	$13.25	Monthly	Bailey, Bryan-Long Term Disability	13.25
9	Bailey, Bryan	Active	Police	Short Term Disability	Metropolitan Life Ins Co	STD	Enrolled	04/01/2018	60% of earnings up to $1500 Per Week		$0.00	Monthly	$18.34	Monthly	Bailey, Bryan-STD	18.34
10	Beesting, Alexandra	Active	Planning Department	AD&D	Metropolitan Life Ins Co	AD&D	Enrolled	04/01/2018		$50,000.00	$0.00	Monthly	$1.40	Monthly	Beesting, Alexandra-AD&D	1.40
11	Beesting, Alexandra	Active	Planning Department	Employee Life	Metropolitan Life Ins Co	Basic Life	Enrolled	04/01/2018		$50,000.00	$0.00	Monthly	$8.60	Monthly	Beesting, Alexandra-Basic Life	8.60
12	Beesting, Alexandra	Active	Planning Department	Long Term Disability	Metropolitan Life Ins Co	Long Term Disability	Enrolled	04/01/2018	60% of earnings up to $7000 Per Month		$0.00	Monthly	$21.55	Monthly	Beesting, Alexandra-Long Term Disability	21.55
13	Beesting, Alexandra	Active	Planning Department	Short Term Disability	Metropolitan Life Ins Co	STD	Enrolled	04/01/2018	60% of earnings up to $1500 Per Week		$0.00	Monthly	$29.83	Monthly	Beesting, Alexandra-STD	29.83
14	Bolick, Joshua L	Active	Police	AD&D	Metropolitan Life Ins Co	AD&D	Enrolled	04/01/2018		$50,000.00	$0.00	Monthly	$1.40	Monthly	Bolick, Joshua L-AD&D	1.40
15	Bolick, Joshua L	Active	Police	Employee Life	Metropolitan Life Ins Co	Basic Life	Enrolled	04/01/2018		$50,000.00	$0.00	Monthly	$8.60	Monthly	Bolick, Joshua L-Basic Life	8.60
16	Bolick, Joshua L	Active	Police	Child AD&D	Metropolitan Life Ins Co	Child AD&D	Enrolled	04/01/2018		$10,000.00	$2.90	Monthly	$0.00	Monthly	Bolick, Joshua L-Child AD&D	2.90
17	Bolick, Joshua L	Active	Police	Child Life	Metropolitan Life Ins Co	Child Life	Enrolled	04/01/2018		$10,000.00	$1.60	Monthly	$0.00	Monthly	Bolick, Joshua L-Child Life	1.60
18	Bolick, Joshua L	Active	Police	Long Term Disability	Metropolitan Life Ins Co	Long Term Disability	Enrolled	04/01/2018	60% of earnings up to $7000 Per Month		$0.00	Monthly	$11.94	Monthly	Bolick, Joshua L-Long Term Disability	11.94
ADP

Cell Formulas
Range	Formula
O2	=A2&"-"&F2
O3	=A3&"-"&F3
O4	=A4&"-"&F4
O5	=A5&"-"&F5
O6	=A6&"-"&F6
O7	=A7&"-"&F7
O8	=A8&"-"&F8
O9	=A9&"-"&F9
O10	=A10&"-"&F10
O11	=A11&"-"&F11
O12	=A12&"-"&F12
O13	=A13&"-"&F13
O14	=A14&"-"&F14
O15	=A15&"-"&F15
O16	=A16&"-"&F16
O17	=A17&"-"&F17
O18	=A18&"-"&F18
P2	=M2+K2
P3	=M3+K3
P4	=M4+K4
P5	=M5+K5
P6	=M6+K6
P7	=M7+K7
P8	=M8+K8
P9	=M9+K9
P10	=M10+K10
P11	=M11+K11
P12	=M12+K12
P13	=M13+K13
P14	=M14+K14
P15	=M15+K15
P16	=M16+K16
P17	=M17+K17
P18	=M18+K18

 

[tsh1586]

that is the only criteria that I have that is unique. I created the name and plan combined to create a unique combo.

 

[tsh1586]

thank you posted sample data

 

[Fluff]

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]

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]

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