Fuzzy VLOOKUP using a Range of Values

[sys8dmin]

Hi,

I have a problem where I am trying to VLOOKUP a certain code from a range but the source field has the target string in a larger "messy" string.

E.g. trying to search the cell "Y:\Certificates awaiting approval\1 Station Road TMV Dalex 090118.pdf" for the string "1 Station Road" and return the code "/1 Station Road, TOWN 5135/".

I have looked at using a fuzzy VLOOKUP using "*"&A!&"*" but had no success.

Is there an easier way to do this? I have 60,000 row spreadsheet to input the codes for so hoping to find a solution.

Thanks!

 

[sys8dmin]

Screenshots:

https://imgur.com/a/xmjCU3H

 

[Chameleon64]

I think this can be done using the TRUE parameter of VLOOKUP which uses approximation. Assuming your cells with the source text all start with "Y:\Certificates awaiting approval" and your table with the codes is sorted by street name then use the following formula in B2:

VLOOKUP(RIGHT(A2,LEN(A2)-34),CodeTableRange,2,TRUE)

This strips the first part of the source code before performing the approximate lookup.