How to vlookup the characters between a delimiter

[phillipc1]

Hello,

I am trying to do a vlookup that pulls out the employee number from a field that contains the employee name and employee number, without the "()" delimiter. However, I am at a loss on what type of formula to use. Below is a sample of what I am trying to do. I need to populate Employee Number.

Manager Lookup.xlsx
	A	B
1	Record	Employee Data
2	1	Jason Bourne (12345)
3	2	Tim Drake (54321)
4	3	Luke Skywalker (12131)
5	4	Darth Vader (54535)
Sheet3

Manager Lookup.xlsx
	A	B	C
1	Record	Employee Name	Employee Number
2	1	Jason Bourne
3	2	Tim Drake
4	3	Luke Skywalker
5	4	Darth Vader
Sheet2

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(LEFT(VLOOKUP(A2,Sheet3!A:B,2,FALSE),FIND("(",VLOOKUP(A2,Sheet3!A:B,2,FALSE))-1),"")

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	Record	Employee Name	Employee Number
2	1	Jason Bourne	12345
3	2	Tim Drake	54321
4	3	Luke Skywalker	12131
5	4	Darth Vader	54535
Sheet2

Cell Formulas
Range	Formula
B2:C5	B2	=SUBSTITUTE(TEXTSPLIT(XLOOKUP(A2,Sheet3!$A$2:$A$100,Sheet3!$B$2:$B$100,""),"("),")","")
Dynamic array formulas.

 

[phillipc1]

Thanks Fluff, that did it. Have a great week!

 

[Fluff]

You're welcome & thanks for the feedback.