How to modify the current macro that finds a pair of data in a column to return with the value in a different column?

On the attached spreadsheet I have a macro that will search for a pair of text listed in cells “G2:G3” and return with True or False.  I need it to find the pair and return with the value in column “A” in the same row that the value in “G2” portion on the pair was found in.
Pair-Function.xlsm
kbay808Asked:
Who is Participating?
 
byundtCommented:
Here is a user-defined function that will search column C for two successive values and then return a value from column A. You use it with a worksheet formula like:
=Pairs2(G2,G3,C:C,A:A)
Function Pairs2(strFirst As String, strSecond As String, rngSearch As Range, rngReturn As Range) As Variant
Dim i As Long, n As Long
Dim v As Variant
Dim rng As Range
Set rng = Intersect(rngSearch, rngSearch.Worksheet.UsedRange)
n = rng.Rows.Count
v = ""
For i = 1 To n
    If LCase(rng.Cells(i, 1).Value) = LCase(strFirst) Then
        v = rngReturn.EntireColumn.Cells(rng.Cells(i, 1).Row).Value
    ElseIf LCase(rng.Cells(i, 1).Value) = LCase(strSecond) Then
        If v <> "" Then
            Exit For
        ElseIf rng.Cells(i, 1).Value <> "" Then
            v = ""
        End If
    End If
Next
Pairs2 = v
End Function

Open in new window

Pair-FunctionQ28643310.xlsm
0
 
kbay808Author Commented:
That worked great!  Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.