Vlookup the first 9 letters

[Troy73]

Is there a way to lookup only the first 9 letters in a range in VBA? Or, can I match a value that's in A60 of Sheet1 to a range in Sheet2 and omit the last word in the source cell "trainee" and find the 1st instance where that matches?

[Richard Buttrey]

Hi,

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[Troy73]

On attached workbook.
If SHeet1 has a value in B4 the make C4 = EE_Nam. Then Vlookup A4 (as the source to find) but omit the letters "Trainee", so the source value would be "Make-Up Foreperson". Vlookup "Make-Up Foreperson" in the range SHeet2 Range("A:A"). Once found then count down 4 rows and insert a row and add the name there.

[Troy73]

Nevermind, I figured it out.

[FDibbins]

What was your "fix"? Care to share?

[Troy73]

Trainee_Position = Left(Trainee_Position, Len(Trainee_Position) - 8)

I just omitted the last 8 characters then I could properly look up the correct value.

[FDibbins]

Thanks for sharing