Offset after first match until the first occurrence of a given string

[Bering]

Hello,

I have uploaded an image of my data and the expected result highlighted in yellow.

My goal is to match ABC (or BCE, CDE etc), then offset until the first occurrence of the string "swift" is found below and get the corresponding value in column D.

No luck so far.

Thank you.

 

[Cubist]

Try

Sub FindSwift()
    Dim a, b
    Dim i As Long, j As Long
    Dim numRow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet2") 'change sheet name as needed
   
    numRow = ws.Cells(Rows.Count, "C").End(xlUp).Row '
    a = ws.Range("C3:D" & numRow).Value
    ReDim b(1 To numRow, 1 To 1)
    j = 1
 
    For i = 1 To numRow - 2
        If a(i, 1) = "swift" Then
            b(j , 1) = a(i, 2)        
            j=j+1
        End If
    Next i
    ws.Range("J3").Resize(j - 1, 1).Value = b
End Sub

 

[Bering]

Try

Sub FindSwift()
    Dim a, b
    Dim i As Long, j As Long
    Dim numRow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet2") 'change sheet name as needed
 
    numRow = ws.Cells(Rows.Count, "C").End(xlUp).Row '
    a = ws.Range("C3:D" & numRow).Value
    ReDim b(1 To numRow, 1 To 1)
    j = 1
 
    For i = 1 To numRow - 2
        If a(i, 1) = "swift" Then
            b(j , 1) = a(i, 2)      
            j=j+1
        End If
    Next i
    ws.Range("J3").Resize(j - 1, 1).Value = b
End Sub

Thank you. I see an issue now. My goal is to first match the code in column I with those in column C and then get the corresponding swift value in column D.

 

[Cubist]

Are you saying the values in I are not in order?

 

[Bering]

Are you saying the values in I are not in order?

they could be in any order, and I need the swift only for the ones in the list, i.e. I could have only 3 of them and the result should be

ABC 123
GHI 555
EFG 648

Sorry

 

[Cubist]

Going back to the first code. You can change the location output. This can serve as your lookup table, then you can just do a VLOOKUP. Change "I3" to whereever you want it to output on the sheet.

ws.Range("I3").Resize(j - 1, 2).Value = b

 

[Bering]

Thank you for your help.
I eventually resorted to the formula route:

INDIRECT(ADDRESS(ROW(INDEX(C:C,MATCH(I3,C:C,0)))+ROW(INDEX(C:C,MATCH("swift",INDIRECT(ADDRESS(ROW(INDEX(C:C,MATCH(I3,C:C,0))),3,4)&":C100"),0)))-1,4,4))