Lookup function that searches for a cell under a particular "Heading"

[ctstarz94]

Hello all, apologies if my title was unclear.


So I have two workbooks, one is a workbook in which i am doing the lookup function in. The other is an "input" workbook which is replaced by a new one periodically.

So two problems:
1. The "input" workbook has multiple row items of the same designation (I'm looking for a row "core growth" however there are multiple "core growth" line items for different regions).
2. The row items may change from workbook to workbook (I.E core growth for region A could be in row 25 in one workbook, row 29 the next, and so on and so forth).

So my question is it possible for excel to only look for "core growth" on the few rows that come after a heading "Region a"

Thank you!

 

[Max1616]

Hello, this is definitely possible, but it's hard to provide you with specific code without more info about what you're looking at. Here's an example of how a loop could accomplish what you're looking for, but it will probably need adjustments based off whatever your situation looks like:

Sub Region_Lookup()

Dim InputWB As Workbook
Dim LookupWB As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim NumRows As Integer
Dim LookupValue As String
Dim LookupRegion As String
Dim Output As Range
Dim i As Long
Dim j As Long

Set InputWB = Workbooks("Input") 'Input workbook name
Set LookupWB = Workbooks("Lookup") 'Lookup workbook name

Set ws1 = InputWB.Sheets("Sheet1") 'You can edit these if the lookup/inputs are on soemthing other than sheet 1
Set ws2 = LookupWB.Sheets("Sheet1")

Set Output = ws1.Range("C2") 'Setting the output of the macro to Range C2 in the input workbook

NumRows = 10 ' This 10 means that the loops will look for your lookup value within 10 rows of the region
LookupValue = ws1.Range("A2") 'Change this if your lookup value is in a different spot
LookupRegion = "Region " & ws1.Range("B2") 'Change this if your lookup region is in a different spot


For i = 2 To ws2.Cells(Rows.Count, 1).End(xlUp).Row
    If LCase(ws2.Cells(i, 1)) = LCase(LookupRegion) Then
        For j = i To i + NumRows
            If LCase(ws2.Cells(j, 1)) = LCase(LookupValue) Then
                Output = "Row " & j 'If the lookup is found, return the row number it is found in to the output range
                Exit Sub
            End If
        Next j
    End If
Next i

End Sub

Here's the input spreadsheet I'm using (Output of the macro is C2):

Here's the lookup spreadsheet: