Vlookup or Index/Match?

fozo

New Member
Joined
Mar 13, 2013
Messages
24
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.
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi,

Can you post some sample data so we can see whats happening?
 
Upvote 0
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
ABCDE
1First NameLast NameNameDepartmentCompany
2JohnWilliamsJohn WilliamsA1B2
3JohnSmithJohn SmithA2B3
4JoeBlogsJoe BlogsA3B4
Sheet1



Book1
ABC
1NameDepartmentCompany
2John K. WilliamsA1B2
3John B. SmithA2B3
4Joe D. BlogsA3B4
Sheet1
Cell Formulas
RangeFormula
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))
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

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