Searching for column values of one table with substring value of the column present in another table

[Anonymous]

Table 1:

Product    SSN

Bag           123: Yellow bag

Soap         234: Lux super saver, 4568: Himalaya 

Table 2:

ID        Quantity

234      4

456      7

I need to search for the ID field of Table 2 to the numbers before the ':' in Table 1 and add a new status column in Table 2. If there is a match then 'Match' should be returned else 'Not matched' should be returned in the status column. 

Ex: 

output

ID       Quantity    Status

234    4                 Matched

456    7                 Not matched

I tried various options like Text.contains, search, etc. They don't seem to be working

[TeigeGao · Accepted solution]

Hi @Anonymous ,

Based on my test, we can't do that.

Generally, we will use lookupvalue() function to find data in another column, but this function cannot support fuzzy query, only search() function can support, but we can't combine lookupvalue() and search() function.

Best Regards,

Teige