Lookup, index, match ,address ??????

Hello once again people Ok now, I no what I'm about to ask can be done using a macro and I probably could of written it in less time that it took me to write this post but I'm sure it could be done using worksheet functions? It's given me a real headache so would someone step up to the plate please?

Problem is:

I have a stock code 6815JJPV

6815 being the true "code"
JJ representing the size
PV representing the material.

When 6815JJPV is ordered it's copied to a sheet listing all outstanding jobs but the sheet fragments the full code into 6815JJ and PV in order to make the reporting on how much PV we are using easier. If you look at the attached workbook there is a small sample on sheet 1 of the outstanding jobs list. What I would like to be able to do is from another workbook type 6815JJPV into a cell and have the cell next to it - say "B1" look up 6815JJPV from the outstanding jobs sheet then return the PO No from column A. Now I no that things would be easy if the full code wasn't fragmented, they would also be easy if there was another column using the "&" symbol to join the strings thus giving a full code to just run INDEX(MATCH on but that isn't the case sadly and they wont let me change the layout of things so, I'm trying to stealth around all the crap to make my job easier... I hope all this makes sense?

By the way I did also think about using the LOOKUP function and the ADDRESS function but it gets tricky because it more often that not the case that same item will be ordered in 2 or 3 different materials which could lead to the PO No for 6815JJWV being returned because it is positioned lower in the list that 6815JJPV. What this function would really have to do in short is lookup 6815JJ then if the material next to it is PV obtain the PO No in column A. And like I said I no I could of written a macro by now lol but all these functions feel so close to what I need - I can't help but think it IS possible but I just don't no how ....


And if one of you comes back with a really simple short answer I'll be gutted hahahah

Thanks in Advance ! - James