A better way to store/retrieve bulk data?

PowerPivot is probably not the answer here (doesn't sound like pivot tables will help you).

However, your big time lag is having the code loop through every cell in the range. I would suggest altering the custom function to make use of the .find() method and the Union operation.

Basically, you can greatly improve things by building a range that only contains 'hits'. You can then do what you will with the values in those cells. By doing this, instead of looping 150000 times... you will only loop for the number of matches.

Would go something like this code below:

Function findMatchRange(searchRange as range, lookFor as string) 

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

For Each hit in matches
'do what you will with the value here, i wasn't really sure what you were doing :(
next hit

End Function

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