Lookup with certain criteria

[Jonker]

Hello everyone, thanks for taking the time to read this and hopefully help me out.

I have a huge list of part numbers in sheet3 and want to compare with another list on sheet1, but they are a little different, lists are like this:

sheet3:

T491T336M004ZT
T491T336K004ZT
T491T336M004ZT7280
T491T336K004ZT7280

T491T476M004ZT
T491T476K004ZT
T491T476M004ZT7280
T491T476K004ZT7280

sheet1:

T491A336(1)003A(2)
T491A335(1)004A(2)
T491A475(1)004A(2)
T491A685(1)004A(2)
T491S685(1)004A(2)
T491B106(1)004A(2)
T491A106(M)004A(2)
T491S106(1)004A(2)
T491R106(1)004A(2)
T491B156(K)004A(2)
T491A156(1)004A(2)
T491T156(1)004A(2)
T491S156(1)004A(2)


As you can see, the first ones are grouped by similar numbers... for example, the first 2 numbers are identical except for the M/K (9th character) and on sheet1 there are similar numbers but instead of M/K they have (1), (K) or (M).
How can I set up a formula that will lookup, for example; take this number from sheet3 T491T336M004ZT (first one in example), and in this case I'd like to get in return T491T336(1)004.
The last 2/6 characters don't matter, I just need it to return the same as what I'm looking for (if there is) regardless of the M/K, so in this case, I'd get the same result for the first 4 numbers and so on.

Hope I made myself clear!
Thanks for your time and support!

[DonkeyOte]

Not entirely sure I follow but perhaps:

Please Login or Register  to view this content.

ie you can use wildcards in VLOOKUP etc if using 0/TRUE as final (otherwise optional) parameter.