looking up value in second sheet

[Danny54]

Good Afternoon

I have a workbook that contains 2 worksheets whereas I need to do some kind of lookup between the two and create a new worksheet sheet3

Sheet1

Com	Circuits
a	1102023
b	945629, 1102123

Sheet2

Market	ID	Name
	945629​	Jenny
	1102123​	Mary

Desired Output
Sheet3

Com	Circuits	Name
a	1102023	Mary
b	945629, 1102123	Jenny, Mary

I need to lookup the values Sheet1 (Circuits) in Sheet2 (ID) and return the Name to Sheet3 (Name)

Is there a vlookup or something what would do the trick?

Thanks

 

[kweaver]

Are you providing the Circuits on Sheet3 and need the names from Sheet2?

 

[kweaver]

I assumed you provide column A in Sheet3, then I used XLOOKUP for the B column and a mix of things for the C column.
Is this even close?

Sheet2

Market	ID	Name
	99999​	Susanne
	945629​	Jenny
	1102123​	Mary
	12345​	Kevin
	55555​	Danny

Sheet3

Book7
	A	B	C
1	Com	Circuits	Name(s)
2	a	1102123	Mary
3	b	945629, 1102123	Jenny, Mary
4	c	12345, 55555, 99999	Kevin, Danny, Susanne
Sheet3

Cell Formulas
Range	Formula
B2:B4	B2	=XLOOKUP(A2,Sheet1!A2:A11,Sheet1!B2:B11)
C2:C4	C2	=TEXTJOIN(", ",,INDEX(Sheet2!$C$1:$C$11,MATCH(FILTERXML("<x><y>"&SUBSTITUTE(B2,",","</y><y>")&"</y></x>","//y"),Sheet2!$B$1:$B$11,0),1))