@Golfnhockey12 in that case I'd opt for a VBA solution. The below code will do what you want. Install it in the workbook you want it run in.
Sub ReplaceVendors()
Dim SourceSheet As Worksheet
Dim CriteriaSheet As Worksheet
Dim DataRange As Range
Dim RowIndex As Long
Dim CriteriaIndex As Long
Dim Criteria As Variant
Dim Source As Variant
Dim Values As Variant
Set SourceSheet = ThisWorkbook.Worksheets("shopexcel")
Set CriteriaSheet = ThisWorkbook.Worksheets("Criteria")
With SourceSheet
Set DataRange = .Range("A2", .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column))
Source = DataRange.Value
End With
With CriteriaSheet
Criteria = .Range("A2", .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column))
End With
ReDim Values(1 To UBound(Source, 1), 1 To 1)
For RowIndex = LBound(Source, 1) To UBound(Source, 1)
Values(RowIndex, 1) = Source(RowIndex, 4)
For CriteriaIndex = LBound(Criteria, 1) To UBound(Criteria, 1)
If InStr(1, LCase(Source(RowIndex, 3)), LCase(Criteria(CriteriaIndex, 1)), vbTextCompare) > 0 Then
Values(RowIndex, 1) = Criteria(CriteriaIndex, 2)
Exit For
End If
Next CriteriaIndex
Next RowIndex
SourceSheet.Range("D2:D" & DataRange.Rows.Count).Value = Values
End Sub
HTH
