How to tweek table array in Vlookup function

New Contributor
I want to apply Vlookup but lookup value is 314-645 and table array's first column has value GHJ-314-HTY-645-KPU
Now how can I tweek table array'first column data in formula itself because I cannot make change in table array sheet. This is use of very frequently so this takes lot of time if i copy table somewhere else and make changes into it.
Please suggest solution
Thanks in advance.
2 Replies
best response confirmed by Hans Vogelaar (MVP)

@Mojibure I would go for INDEX and MATCH in stead of VLOOKUP, as demonstrated in the picture below:


Of course, this assumes that the digits are always in the same spot within the larger string.


Thank you Riny.
This looks awesome.