vlookup

[tsh1586]

Hello All,
I am looking to match the last name and the first letter of the first name for my employees and return the name as listed on another sheet based on the partial match.
So on my first sheet ListBill I have the names listed as last name, first name and middle initial in column L. Column A of this worksheet has the last name and Column B of that sheet contains the First Name and Middle Initial. Column L is the combining of those two columns.
Second Sheet named ADP is data pulled from another program it contains the last name, first name and middle initial in column A. The problem is the last name, first name middle initial from one sheet doesn't exactly match the second sheet. I am needing a formula that matches the ADP column A (first name last name middle initial) with the column L in the ListBill worksheet (combined last name first name and middle initial). I am currently using the Vlookup to match but am not using the false criteria for perfect match thus if there are multiple employees with the same last name it is returning the first one it comes to. I want to match the last name, and the first initial of the first name.
Does anyone have a solution?
I am currently using the formula below which would work great if there was not duplicates
=VLOOKUP(A2,ListBill!$A$39:$L$426,12)

 

[Special-K99]

Just a thought, not saying I can fix this, there are no salutations, like Mr, Mrs, Col, etc ? Or if there are they are stored in a cell away from the last name/first name values ?

 

[jtakw]

Hi,

Can you show some sample data?

 

[tsh1586]

my copy and paste will not work I tried using the HTML Maker but gives a bunch of stuff not in columns like it use to be

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
39	BAHNWEG	NICHOLAS J	xxxxxxx7972	1	IT DIV	1	AD&D	Member only	$ 1.40	50000		BAHNWEG,NICHOLAS J	AD&DMember only	AD&D	BAHNWEG,NICHOLAS J-AD&D	$ 1.40	$ -	$ 1.40	$ -	$ 1.40	$ -	$ -
ListBill

Cell Formulas
Range	Formula
L39	=A39&","&B39
M39	=G39&H39
N39	=VLOOKUP(M39,Vlookup!$C$2:$D$14,2,FALSE)
O39	=L39&"-"&N39
P39	=IFERROR(VLOOKUP(O39,ADP!$R$2:$S$347,2,FALSE),"0")
Q39	=IF((OR(E39="ABC",P39="0")),0,IFERROR(VLOOKUP($O39,ADP!$R$1:$U$347,3,FALSE),""))
R39	=IF((OR(E39="ABC",P39="0")),0,IFERROR(VLOOKUP($O39,ADP!$R$1:$U$347,4,FALSE),""))
S39	=IF(E39="ABC",I39,0)
T39	=Q39+R39
U39	=P39-T39
V39	=I39-P39-S39

The second Sheet the following is the top line

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	Bahnweg, Nicholas	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	AD&D	BAHNWEG,NICHOLAS J-AD&D	1.40	$0.00	$1.40
ADP

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,ListBill!$A$39:$L$426,12)
Q2	=TRIM(H2)
R2	=B2&"-"&Q2
S2	=O2+M2
T2	=M2
U2	=O2

 

[jtakw]

See if this helps you:

1. Change your ListBill Sheet L39 formula to include a Space after the comma, like this:

=A39&", "&B39

2. Use this formula in B2 of the ADP Sheet:

=LOOKUP(2,1/SEARCH(A2,ListBill!$L$39:$L$426),ListBill!$L$39:$L$426)

This matches the Entire name as shown in ADP A2 to ListBill Column L with or without the middle initial.