Vlookup using beginning of string

Dear Excel experts,
I have a file with data(worksheet “data”), in which there are 2 specific columns by name “From Position” & “To Position”, considering their text we require Location basis worksheet “reference table”
result require considering text from "From Position" to "Location from" and text from "To Position" to "Location To" considering reference table

For example (referring prefix and reference table )
From Position Y-GTI-01C33E1 i.e. with prefix Y-GTI-0 Location will be Vessel/Yard
From Position Y-GTI-BMCT i.e. with prefix Y-GTI-B Location will be BMCT
Like wise..
i have updated first two records manually in "Location from" and "Location To" for ready reference
Kindly help
Thanks in advance
RavindraK

Thank you much Sir for very quick and appropriote revert/answer, this is really good , however, as we are having approx. 2 lacs of records in a file, can we have a macro /vba for the said process, which will be faster than formula.

Kindly help, if possible.
thanks a lot in advance.

RavindraK

Originally Posted by RavindraK

... from "From Position" to "Location from" and ... from "To Position" to "Location To"

Maybe such a simple listing macro ?
(due to firewall blockage the macro is in the txt file)

Thank you so much Sir, Macro is working exactly as expected, You are Really charm !!
Can you please explain (annotations) this code for me, if possible.
WBRegards
RavindraK

Thanks a lot . This is help me lot to fix my problem as well understand the macro script !!
WBRegards
RavindraK