match the correct team names of Database A into Database B

Hello Xelers,
I face the issue, that I need to match 2 Databases with its team names.
The names in Database A are the correct ones.
The names in Database B needs to be exchanged with the correct names from Database A

I put 3 samples in line 3,4 and 5 for better understanding.

I am attaching the file "matches.xlsx"

The order of the listed names in column A should not be chaged as the names will be copied (after the correct name is set) back into the original file.
It can happen that names appear several times.

It can also be that names do not have a match at all from database A

Maybe a VBA can solve my query.

Is there anyone who can hels tp solve this issue?

Thank you very much in advance.
Thomas

You cannot replace names with a formula. You'll need VBA for this. Shall I move the thread for you?

You ought to provide at least 3 or 4 examples in the sample file - perhaps highlight the matching rows to show which data you want.

These are fuzzy replacements, which can be tricky. I'm moving the thread for you now.

To provide a solution please tell us..
If there are multiple matches for a club how to know which is correct?

For example, if in Database B is Austria Klagenfurt does it match to Austria or to Klagenfurt?
Because Austria Vienna might also match to Austria...

It's clear to me but it's not clear to Excel..
If it say Austria Klagenfurt I can set that it return last word, Klagenfurt.. But in other case, for example, Heidelberg United
it will return United that is common word..

There is no formula: return me logically sometimes first and sometimes second..

In such fuzzy logic database I would suggest to create new table in sheet 2 where you will put all variations...

For example:

Kiffen             	Kiffen
Aston Villa     	Aston Villa
Atletico Madrid   	Atletico
Austria Klagenfurt	Klagenfurt
Heidelberg United	Heidelberg

In that case you will be able to VLOOKUP all names

Don't give up just yet...
Let's first check what possible matches there are up to 2...
Look at results here and then we can get some feedback and possibly refine to get as close as...

There are members proficient in Regex which could get this perfected...Sadly I am not one of them...

Sub J3v16()
Dim Data, Chk, Str, Crit, Dict As Object, Fnd As Boolean, lr As Long, i As Long, ii As Long
Set Dict = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, 1).End(xlUp).Row
With Cells(7, 1).CurrentRegion.Resize(, 5)
    .Cells(2, 2).Resize(lr, 3).ClearContents
    Data = .Value
    For i = 2 To UBound(Data)
      Dict.Item(Data(i, 5)) = 1
    Next i
    For i = 2 To lr
        If Dict.Exists(Data(i, 1)) Then
            Data(i, 2) = Data(i, 1): Fnd = True
        Else
            Fnd = False
            Str = Split(Data(i, 1), " ")
            If UBound(Str) > 0 Then
                For ii = 0 To UBound(Str)
                    If Len(Str(ii)) > 2 Then
                        Crit = IIf(ii = 0, Str(ii) & "*", "*" & Str(ii) & "*")
                        Chk = Application.Match(Crit, .Columns(5), 0)
                        If Not IsError(Chk) Then
                            Data(i, 3) = Data(Chk, 5)
                            Fnd = True
                            Exit For
                        End If
                    End If
                Next ii
            Else
                Data(i, 2) = "NO MATCH"
            End If
            If Fnd = False Then Data(i, 2) = "NO MATCH"
        End If
    Next i
    .Value = Data
End With
End Sub