Text select and sort with values

[fishinglittlepine]

I have a workbook with 2 worksheets.
Worksheet 1"Total Resources", worksheet 2 "Pricing"
"Total Resources" has data in 4 columns, Code, Description, Currency, Value.
"Pricing" has 2 columns, "Part No." and "Value"
In "Total Resources", under Description the text contains the Part No. in the last string of that text. The Part No. appears in multiple lines of the Description.
There are 836 Descriptions and 80 Part No.
I cannot seem to be able to work how to recognise the relevant Part No. text contained within the Description, then insert the Value from the "Pricing" worksheet into the Value in the "Total Resources" worksheet?

Worksheet 1 "Total Resource"
<TABLE style="WIDTH: 915pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1220><COLGROUP><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7862" width=215><COL style="WIDTH: 658pt; mso-width-source: userset; mso-width-alt: 32073" width=877><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=215>Code</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 658pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=877>Description</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>Currency</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>Value</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>E CT C-AW-02C-001</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Termination, Control Cable PVC/SWA/PVC, AW Glands, 1.5mm2 2C+E Cable OD - 14.1mm / ALCAW16</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65></TD></TR></TBODY></TABLE>
(836 lines long)

Worksheet2 "Pricing"
<TABLE style="WIDTH: 135pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=180><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=116>PART No.</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=64> VALUE </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>ALCAW16</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> $ 14.58 </TD></TR></TBODY></TABLE>
(80 lines long)

Help would be appreciated.

Vince

 

[Haseeb Avarakkan]

Hello,

If your part # always at the end of description & also after the "/", try

D2, copy down...

=INDEX(Pricing!B:B,MATCH(TRIM(RIGHT(SUBSTITUTE(B2,"/",REPT(" ",50)),50)),Pricing!A:A,0))

 

[fishinglittlepine]

Thanks for your quick reply.
Some of the lines only have a SPACE and no /. Works on the others though? How can I resolve the SPACE ones?

Vince

 

[Haseeb Avarakkan]

If so, just change the "/", to " ",

=INDEX(Pricing!B:B,MATCH(TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",50)),50)),Pricing!A:A,0))

Also assume, there is no space in the part #

EDIT: Or, Try this,

=LOOKUP(1E+307,SEARCH(Pricing!$A$2:$A$100,B2),Pricing!$B$2:$B$100)

 

[fishinglittlepine]

Very cool.

Thank you very much for your assistance.

Vince

 

[fishinglittlepine]

That last one only came up a result of 0.

I don't understand the 1E+307?

First one worked though.

Thanks again.

Vince