Vlookup or Index/Match?

[fozo]

Hello Excel gurus,

I am doing our employee database maintenance between our Payroll Database and NetSuite database. However, I am running into some issues that I need your expert help.

The problem I have is our payroll database have employees (without middle name initial) and correct updated their married name. Our NetSuite, in the other hand, has employee with middle name initial and has either go by name or their maiden name.

On Payroll work book, I have First Name, Last Name, Name, Department, and Company
On NetSuite work book, I have Name (include middle initial), Department, and Company.

Goal: trying to validate NetSuite department and company against payroll workbook.

How or what formula I need to use in order to achieve this goal?
Thanks much.

 

[RasGhul]

Hi,

Can you post some sample data so we can see whats happening?

 

[RasGhul]

Fozo,

This will work as long as the format of the names with initial are as below. We will need to check the cell ranges against your sample data as well;

Book1
	A	B	C	D	E
1	First Name	Last Name	Name	Department	Company
2	John	Williams	John Williams	A1	B2
3	John	Smith	John Smith	A2	B3
4	Joe	Blogs	Joe Blogs	A3	B4
Sheet1

Book1
	A	B	C
1	Name	Department	Company
2	John K. Williams	A1	B2
3	John B. Smith	A2	B3
4	Joe D. Blogs	A3	B4
Sheet1

Cell Formulas
Range	Formula
B2	=INDEX([Payroll.xlsx]Sheet1!D$2:D$4,MATCH(LEFT($A2,SEARCH(" ",$A2)-1)&RIGHT($A2,LEN($A2)-SEARCH(".",$A2)-1),INDEX([Payroll.xlsx]Sheet1!$A$2:$A$4&[Payroll.xlsx]Sheet1!$B$2:$B$4,),0))