Comparing known data to text string

[DinoBeGood]

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

[Gregb11]

Please read the yellow banner at the top of the page and supply a good example workbook.

[DinoBeGood]

Sorry about that....thank you for the correction.

[Gregb11]

Do you really mean, "If ANY of the text matches..."?

Cell C7 contains "456" which also matches the "456" in ABC456ABC. There's a "3" in cell C7 also, which matches ABC123ABC. So according to your logic, the result in D7 should be "XYZ123XYZ"
I think you want to have more constraints on what matches.

[windknife]

Maybe,

D7
=IF(C7="","",INDEX(B:B,AGGREGATE(15,6,ROW($A$7:$A$100)/ISNUMBER(FIND(TEXTSPLIT(C7,{"."," ","_"}),$A$7:$A$100)),1)))

copied down.

[DinoBeGood]

windknife....thank you so much! that works perfectly.

[windknife]

You are welcom.