Searching for Multiple Values using Match and Index

[ayrus]

Hi,

I have following Master data defining Code and its Value (with duplicates)

 
Sno
Code
Value

1
VB
Find 1

2
VB
Find 2

3
VB
Find 3

4
VB
Find 4

5
VB
Find 5

6
BB
FindX 1

7
BB
FindX 2

8
BB
FindX 3

Wondering if it is possible to iterate and extract data using (Match and Index) for following results

  
Code
Match Value
VBA Result

VB
Find 4
Find 4

BB
FindX 2
FindX 2

As it can be seen in the masterData, "VB" has following values - Find1, Find2, Find3 and Find4.
So is it possible to take Code and MatchValue as Function parameters and return result when it matches with MatchValue

Any suggestions will be of great help.

many thanks
Surya

[JKwan]

See if this will do

Option Explicit
Sub test()
    MsgBox GetResults("VB", "Find 4")
    MsgBox GetResults("VB", "Find 6")
    MsgBox GetResults("BB", "Find 4")
    MsgBox GetResults("VB", "Find 4")
    MsgBox GetResults("BBq", "FindX 3")
End Sub
Function GetResults(Crit1 As String, Crit2 As String) As String
    Dim Found As Range
    Dim Found2 As Range
    Dim WS As Worksheet
    Dim f As Long
    
    Set WS = ThisWorkbook.Worksheets("Sheet1")
    With WS
        Set Found = Find_All(Crit1, WS.Range("B2:B9"), , xlWhole)
        If Found Is Nothing Then
            GetResults = Crit1 & " - Not found"
        Else
            For f = 1 To Found.Count
                Set Found2 = Find_All(Crit2, WS.Range("C2:C9"), , xlWhole)
                If Found2 Is Nothing Then
                    GetResults = Crit1 & "-" & Crit2 & " - Not found"
                    Exit Function
                Else
                    GetResults = Crit2
                    Exit Function
                End If
            Next f
        End If
    End With
End Function
Function Find_All(Find_Item As Variant, Search_Range As Range, _
                  Optional LookIn As XlFindLookIn = xlValues, _
                  Optional LookAt As XlLookAt = xlPart, _
                  Optional MatchCase As Boolean = False) As Range


    Dim c As Range
    Dim firstAddress As String
    Set Find_All = Nothing
    With Search_Range
        Set c = .Find( _
                what:=Find_Item, _
                LookIn:=LookIn, _
                LookAt:=LookAt, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=MatchCase, _
                searchformat:=False)    'Delete this term for XL2000 and earlier
        If Not c Is Nothing Then
            Set Find_All = c
            firstAddress = c.Address
            Do
                Set Find_All = Union(Find_All, c)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End Function

[ayrus]

Wow JKWan, many thanks for that response. Let me get back to you shortly..

Much appreciated..