A better way to store/retrieve bulk data?

Ok, take a look at the attached workbook to test this out. I am confident this can greatly speed up your code...

Since you can't upload an example, i put together the function and a simple test Sub to show it in action. Open the workbook, enable the macros and press the button to test the function.
You can step through the code to see it working. Press Alt+F8 to see the macro, select "test" and press "Step Into". Press F8 to take each step.

I have a message box pop up to show the results, and i also list them in the sheet in the A column. I think you had a different final step, but maybe this will be something you can edit to meet your needs.

OPTION EXPLICIT
Sub test()

Dim test As Range, hit As Range, x As Variant

Set test = findMatchRange(ThisWorkbook.Worksheets("Sheet1").Cells.Find("Test Data").EntireColumn, ThisWorkbook.Worksheets("Sheet1").Cells.Find("Test Value").Offset(0, 1).Value)

x = ""
For Each hit In test
    x = x & hit.Value
Next hit
MsgBox (x)

For Each hit In test
   ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = hit.Value
   
Next hit

End Sub


Function findMatchRange(searchRange As Range, lookFor As String) As Range

Dim c As Range, matches As Range, hit As Range
Dim firstAddress As String

With searchRange
  Set c = .Find(what:=lookFor)
  If Not c Is Nothing Then
    Set matches = c
    firstAddress = c.Address
    Do
     Set matches = Union(matches, c)
     Set c = .FindNext(c)
    Loop While c.Address <> firstAddress
  End If
End With

Set findMatchRange = matches

End Function