VLookup full list and return value (Formula fix)

I have a list of 10 URL's in column A, that matches 10 values from a second column (B).

However, in the third column (C) I have a list of 100 URL's. I would like to know from this list if it partially contains one of the words of the first column.
If this is the case, it should return the matching value of the second column. I tried multiple Vlookup options, but can't find the 100% perfect formula

E.g.

Column A, Column B
A, Red
B, Green
C, Blue

Column C
ADF, (it matches "A" so should return "Red" in column D)
BGK, (it matches "B" so should return "Green" in column D)
TCP, (it matches "C" so should return "Blue" in column D)
CPG, (it matches "C" so should return "Blue" in column D)

Does somebody know if this is possible, and how? Help would be really appreciated! As it will automate a huge amount of bulk work for me.

what if it had
ACT ???

A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.


The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

HOW TO ATTACH YOUR SAMPLE WORKBOOK:, Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

is it just coincidance that you are matching now most of the string
anyway
=INDEX($B$2:$B$34,MATCH(RIGHT(C2,LEN(C2)-7),$A$2:$A$34,0))
does that work in the real data - seems to in the sample

as the url is preceded by 7 characters in all cases

otherways to this

But does NOT seem to be the same as original question , which was that a character appeared within a string , not matching the whole string

you have in profile
MS-Off Ver
2024

i dont think office 2024 has been released - is that the version of office - excel you have - or is that the version of an excel 2016/2019/2021 etc

you are welcome