VLOOKUP/ INDEX:MATCH part of string.

I need match some cells-

I can do standard basic Vlookup and INDEX:MATCH but it is a bit more complex here. Essentially I have--

In sheet1 there is:

Colum A.........................Column B
Codes..............................Barcode
1234-type-Black....................123456789
1224-blue-type.....................987654321

In sheet2 there is:
Colum A.........................Column B
[I]Codes.............................[Barcode]
bd-1224-blue
ab-1234-black



I need to a formula (to go in sheet2 column B) that finds a match where the numeric part of the code and colour match in the two sheets (as they codes come in slightly different formats). So it will essentially match parts of a string within cells and as a result insert the relaxant barcode into column B, sheet2.

So in the case of the example given it would be...

Colum A.........................Column B
Codes..............................Barcode
1234-type-Black....................123456789
1224-blue-type.....................987654321

In sheet2 there is:
Colum A.........................Column B
[I]Codes.............................[Barcode]
bd-1224-blue........................987654321
ab-1234-black.....................123456789

Not even sure this is possible but thanks for any help in advance

Strud

See the attached

Massive thanks teethless mama...that's exactly what I was after!

Gonna spend some time getting my head round it now.

Thanks again!

Originally Posted by Teethless mama

See the attached

You're Welcome!