Comparing known data to text string

Hello

I want to be able to take supplied data from another team and compare it to known data on my end. The supplied data isnt always in the same format but it should always include a portion of the known data on my end.

Example:
Column A contains known data. Example: DEF456DEF
Column B contains known data that relates to column A. Example: RST456RST
Column C contains supplied data. Example: CKWXLE.SKE_DEF456_XKJKJ20
Column D would contain the output. Using the examples below, the output would be RST456RST.

Column A and B would have over 1000 rows containing known data. The supplied data could be anywhere from 50 rows to 500 rows of data. So I'd want to take each row any column C and compare it to all data in column A. If a match is found, column D would populate with the info from the same row in column B.

The only way I could get this to work was a VLOOKUP using wildcards but I had to edit the supplied data text in order for a match to be made. I dont have the time to edit each row everytime new data is received. I just want to compare what is provided to known and if any text matches, update column D with info from column B. Any/all help would be greatly appreciated.

Thanks in advance
David