Locate a reference in a table based on specific words on a text

Hi

I'm trying to find the best solution to get the third table with respective codes (bold one):

Excel question.jpg

Can you help me ?

Thanks a lot!

lol


It's to use on engineering parts catalogues - in order to avoid technical stuff, I simply used colours and type of transports to expose the problem - once the solution could be also used on both cases.

so... it isn't homework! (sorry 'bout the way I exposed the problem, but sometimes an image can be better than 1k words )


Thanks in advance