I know how to use a wildcard with Vlookup on the reference value, but what about on the matched column index?

I know you can do for instance VLOOKUP("*Hello*",A4:G4,2,FALSE)

However, what if you wanted to match a cell that is "Hello", with another one that is "Why, Hello there!" (so the opposite i suppose)

My data is set in a way where a macro is going to ask if A4 in workbook 1 matches C2:C25000 in workbook 2 to return the corresponding value in D back to workbook 1. The thought is that when A4 in workbook 1 says Its DHS Here, that I could input a value such as DHS in column C in workbook 2 and have it say its a match.

Is this possible?