Vlookup or other search formula?

Hi all,

I'm trying to match some data to names. The problem is the formatting of everything as it is stored on a database which I extract but am given info in a different format.
e.g

Data: First name, Last name, Other info
Database: Lastname-firstname-number, number

What I want to do is have some kind of lookup to match the 'number' to the name in the data.

I understand I will have to concatenate the names then create some kind of lookup to find the employeename then add the number to the name. The attachment hopefully explains it clearer.

I assume you have 3 sheets as below.

1 sheet having first name and last name
2 sheet having Employee name + number
3 result sheet

ResultSheet

Row\Col	A	B	C	D
1	First name	Last name	Number	Employee name + number
2	Bob	Hoskins	12345	Hoskins,Bob12345

A2

Formula:

Please Login or Register  to view this content.

Copy towards B column

C2

Formula:

Please Login or Register  to view this content.

D2=B2&","&A2&LOOKUP(10^10,SEARCH(B2&","&A2,Sheet2!A2),Sheet2!B2)

Check the attached file.

Thanks for feedback

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.