You love hiding the structure of your data...
Rather then going back and forth with Q & A let me go with a customized function, that corresponds to the following code:
Code:
Function GimmeLuck(ByRef Blk1 As Range, ByRef Blk2 As Range) As Variant
Dim oArr() As String, K As Long, B2CC As Long
Dim I As Long, AllIn As Long
'
ReDim oArr(1 To Blk1.Rows.Count, 1 To 1)
B2CC = Blk2.Columns.Count
For I = 1 To Blk1.Rows.Count
If Blk1.Cells(I, 1) <> "" Then
For j = 0 To 20
If (j > 0 And Blk1.Cells(I + j, 1) <> "") Or Blk2.Cells(I + j, 1) = "" Then Exit For
AllIn = 1
For K = 1 To B2CC - 1
AllIn = InStr(1, Blk1.Cells(I, 1), Blk2.Cells(I + j, K), vbTextCompare) * AllIn
If AllIn = 0 Then Exit For
Next K
If AllIn <> 0 Then
oArr(I, 1) = Blk2.Cells(I + j, B2CC)
End If
If oArr(I, 1) <> "" Then Exit For
Next j
End If
Next I
GimmeLuck = oArr
End Function
Copy the code into a standard module of your vba project.
Then return to Excel and you shall see that the function GimmeLuck is now available.
Its syntax:
GimmeLuck(FirstColumnRange, CriteriaAndResultRange)
For example:
=GimmeLuck(A1:A20, C1:C25)
The two ranges must start from the same row but can have different height because there are several rows of criterias after the last value in column A
The formula must be written as an array formula:
-select in column B from the first row of data to the last row used as FirstColumnRange; so B1:B20 with the given example
-insert the formula in the formula bar
-confirm the formula using the keys Contr-Shift-Enter, not Enter alone
If this doesn’t fit your need then you have to explain wich is the layout of your data and which results you need and where
