The difficult part here is the "partial" match.
What is the criteria for the "partial" match?
the worksheet  function VLookup requires an EXACT match.
I'm a little cautious of the partial part.
I think it's definitely possible by creating a VBA Function
and using it instead of VLookup, but I want to make sure
that's what you want before working on it...
What's the data  look like for each sheet?
Can you provide some examples?