Return / extract substring matching list

Need some help extracting / returning a substring from a string, if that substring matches an entry in a table. Search / Find haven’t worked since the substrings share common sub-substrings (e.g. DR-1 and DR-12 are picked up as the same). The substrings may have any number or types of characters, spaces, etc. and are not uniform so positional extraction (left/mid/right) don’t work. Prefer to keep this function based. Note that the standard names in Table 1 aren’t uniform in length / format / letters / numbers in my actual data, thus I need to look up the string in Table 1 and can’t shortcut based on a pattern. Also, the actual Table 1 has the standard names repeated as each entry represents something different based on the subsequent columns (not important to this issue, other than the possible complicating factor of them being repeated)

The original formula I used, before realizing that search wouldn’t work was:

=IF(B6="","",INDEX(Table1,MATCH(1,SEARCH(Table1[Name],[@Input]),-1),MATCH(Table1[[#Headers],[Name]],Table1[#Headers],0)))

Table1 – Standard Names

Table2 – Input Data and Results