Find heading, the subheading, display value

Hi,

I have a spreadsheet (excel 2000) and it has headings and subheadings in the same column, with values in the next column, as attached.

What I need is to perform a lookup or search that recognises the heading, and finds the correct subheading, and displays the value next to that subheading. i.e.:

If I want "BBB" heading, and "ghi" subheading, the value displayed would be 12.

I have been trying to use the find function to find the heading, but cannot find code to search the Subheading:
Cells.Find("BBB", , xlValues).Activate

I have been rattling my brain trying to find any formula or code to perform this, any help would be much appreciated.

Thank you in-advance.
Matt

I don't know if this is any use:

Sub Search()
    Dim rngHeading As Range, rngSubheading As Range
    Dim strHeading As String, strSubHeading As String
    
    strHeading = InputBox("Enter the heading to find")
    strSubHeading = InputBox("Enter the sub-heading to find")
    
    With Worksheets(1).Range("A1:A10000")   'search this range on sheet1
        Set rngHeading = .Find(What:=strHeading, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart)   'heading search
        If Not rngHeading Is Nothing Then
            Set rngSubheading = .Find(What:=strSubHeading, After:=rngHeading, LookIn:=xlValues, LookAt:=xlPart)  'sub-heading search
            If Not rngSubheading Is Nothing Then
                rngSubheading.Offset(, 1).Activate      'activate the value and display message
                MsgBox "Heading: " & strHeading & vbCrLf & _
                        "Sub-heading: " & strSubHeading & vbCrLf & _
                        "Value: " & rngSubheading.Offset(, 1).Value, vbInformation
            Else
                MsgBox "Heading " & strHeading & " was found, but" & vbCrLf & _
                        "Sub-heading " & strSubHeading & " was not found", vbExclamation
            End If
        Else
            MsgBox "Heading " & strHeading & " was not found", vbExclamation
        End If
    End With

    Set rngHeading = Nothing
    Set rngSubheading = Nothing
End Sub

Hi TJ,

This is almost exaclty what I am after!! Except instead of a user inputing the figures via a txtbox, can the code link to cells on the spreadsheet that automatically configure the search and then source the outcome value into another cell.

What you have already done is great, and I am sure I can figure out what I am after from this but any other help would be great.

Cheers again, you're a legend!!
Matt

Instead of the inputboxes use -

strHeading = Sheets(1).Range("c1").Value
strSubHeading = Sheets(1).Range("d1").Value

and to write the value to the sheet -

Sheets(1).Range("e1").Value = rngSubheading.Offset(, 1).Value

Change the sheet and cell references as required.

Thank you again for all the help, I have been spending ages trying to find this code and now it works like a dream!!!!

Cheers,
Matt

Guys,
Sorry to jump in on a very old thread. Can anybody tell me a simple way how to convert above mentioned subroutine to a function. I wanna use a custom function instead of subroutine as I want to use this function directly in sheet instead of calling a subroutine time and again.
Any response is highly appreciated.

Thanks madam. I will surely post a new thread with link to the current thread.