Index match on two sheets

Glenn,

Thanks for your reaction. Would it be somehow, possible to add another filter to this formula that you posted on last?
A problem that occurs, is that there may be duplicate values in col F on the sheets I am searching on (sheet2 and sheet3). There is a way to outsmart this, by adding a min/max filter.

As seen on sheet1, I am searching with value '10' in cell D8. Now it finds the first value that belongs to value 10. This is 5555. However, the result should be 55555 (sheet3).
This can be done by adding a min/max filter. Is there any way to still add this to the formula? Because of the length of this formula, I honestly don't know where to start.

Cheers,

Coen