Double lookup

[Guest]

Hi,

I have a spreadsheet (excel 2000) and it has headings and subheadings in
column A, with values in column B. As shown below:

AAA
Product 1 20
Product 2 30
Product 3 40
BBB
Product 1 10
Product 2 15
Product 3 20

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 "Product 1" subheading, the value displayed
would be 10.

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

 

[Ron Rosenfeld]

Hi,

I have a spreadsheet (excel 2000) and it has headings and subheadings in
column A, with values in column B. As shown below:

AAA
Product 1 20
Product 2 30
Product 3 40
BBB
Product 1 10
Product 2 15
Product 3 20

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 "Product 1" subheading, the value displayed
would be 10.

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

How do you differentiate a Heading from a SubHeading?

Once you define that, one solution would be to read the table from the
worksheet into an array with three dimensions (Heading, subheading, values).
It should then be fairly simple to do your lookup.


--ron

 

[Guest]

Hi
See if this function meets your requirement

Function MyLookup(ByVal sHeading$, ByVal sSubHeading$) As String
Dim r As Range, r2 As Range, r3 As Range
Set r = Sheet1.Range("A:A").Find(What:=sHeading, LookIn:=xlValues,
LookAt:=xlWhole, MatchByte:=True)
If Not (r Is Nothing) Then
If r.Offset(1, 1).Value <> "" Then
If r.Offset(2, 1).Value = "" Then
Set r2 = r.Offset(1, 1)
Else
Set r2 = r.Offset(1, 1).Resize(r.Offset(1,
1).End(xlDown).Row - r.Offset(1, 1).Row + 1)
End If
Set r3 = r2.Find(What:=sSubHeading, LookIn:=xlValues,
LookAt:=xlWhole, MatchByte:=True)
If Not (r3 Is Nothing) Then
MyLookup = r3.Offset(0, 1).Value
End If
End If
End If
End Function