Lookup of inexact text

[buadeoye]

Hi everyone, 

I have a challenge that I will need your help to resolve.

I have 2 data tables : Table1 contains 10 columns and one of the columns contains Postal Code (full), the second Table2 contains 2 columns (Postal Codes and CityName). However the Postal code contains only some elements of the full Postal Code in the Table1.

For example, Table1 contains PO5 1LH but Table2 contains PO5 or 1LH.

My challenge is to lookup the Post Code in Table 2 and compare with Table1, if there is any match of the first 3 elements or the last 3 elements, the code will return the Name of the City in Table 1.

Please help 🙏. 

Thank you 

[DataInsights · Accepted solution]

@buadeoye,

Try this calculated column in Table2:

City = 
MAXX (
    FILTER ( Table1, CONTAINSSTRING ( Table1[Postal Code], Table2[Postal Code] ) ),
    Table1[City]
)