Based on your excel formula it seems there is a deterministic way to identify these hosts, I would cleanse the data in the target table so it's in the same structure as your source table resulting in the lookup working. Essentially was @Rene Norskov said here
"Rather than using the data / server names as is, you could create a cleansed version of the server name that you use for lookup. The cleansed version could be all uppercase and truncated as required. You could also replace or remove particular words in the server name - words that you don't want to include in the comparison/matching of server names. This is typically a very performant solution."
If there isn't a deterministic way to clean the data, the other suggestions would all work, as far as which performs the best it really depends on the actual length and type of the data being worked on.
When designing your solution, whichever one has to read the fewest characters technically would perform the best.
Left/Mid/Right are better than search as they are a defined number of characters, while search has to search the whole string. Using Regex will perform as well as it does, but it will perform worse that something simple like Left()
How much this all matters really just depends on how large of data you are working with something like 10,000 to 20,000 records it probably doesn't matter,
If you have a really large table 1,000,000+records and very FEW need this logic, it can perform better to split the table into 2, do the expensive string logic on the bad records, and append them back in.
I'd suggest implementing whichever solution seems the best based on your data, and assessing what the impact to your calculation is, most of the time for stuff like this is should be pretty minor, unless as I said, these tables are large and the string values are very long.
#CostingStandard(CT-Foundation)