Finding Named Range Name Based on a cell found within that range.

Sean, I'm sure there are better ways but the below UDF may help ?

Function Hdr(rngCell As Range) As Variant
Dim rngRefers As Range, nm As Name
On Error Resume Next
For Each nm In ThisWorkbook.Names
    Set rngRefers = nm.RefersToRange
    If Err.Number = 0 Then
        If Not Intersect(Range(rngCell.Value), rngRefers) Is Nothing Then
            Hdr = rngRefers.Cells(1, 6).Value
            Exit Function
        End If
    End If
    Set rngRefers = Nothing
Next nm
End Function

Above stored in Module called from cell:

=HDR(M1)

That said if you mix scope of your named ranges it's still open to error I guess... I've also assumed your named ranges commence from Column A.

EDIT: Just noted you said you can already do this in VBA so the above is moot - apologies. Time for me to power down for the day I think...