Lookup partial values and if match return partial value from another cell

Hi,

I've been searching what feels like everywhere for a solution. I understand it might not be simple.

I have attached a file containing an example. In the sheet Expected result you can see what value I expect the formula to return in to H2-H6.

The example is short but I need a formula in the H column (Sheet Workbook 1) that does the following:

Take the first word in E-cell, for example E2 and the first number and the last word, in this case New, 6202 and accounting. I then need the formula to search in (Sheet Workbook 2) column D for a match on New, 6202 and accounting. If the formula finds a match I need it to return the value from adjacent C column but removing the searched number, in this case 6202 and only return 10.

The reason I need this setup is that both workbooks contain thousands of rows and the data can be changed from one time to another. But the target number will always be found in the same way.

Anyone that can think of a solution for my problem?

Br
René