Searching two columns and returning value from third VBA

Answer 1

Let's create the following function in a new Excel module:

Function FindValue(rng1 As Range, rng2 As Range) As Variant
Dim varVal1 As Variant
Dim varVal2 As Variant
Dim rngTargetA As Range
Dim rngTargetB As Range
Dim lngRowCounter As Long
Dim ws As Worksheet

varVal1 = rng1.Value
varVal2 = rng2.Value

Set ws = ActiveSheet
lngRowCounter = 2
Set rngTargetA = ws.Range("A" & lngRowCounter)
Set rngTargetB = ws.Range("B" & lngRowCounter)
Do While Not IsEmpty(rngTargetA.Value)
    If rngTargetA.Value = varVal1 And rngTargetB.Value = varVal2 Then
        FindValue = ws.Range("C" & lngRowCounter).Value
        Exit Function
    End If

    lngRowCounter = lngRowCounter + 1
    Set rngTargetA = ws.Range("A" & lngRowCounter)
    Set rngTargetB = ws.Range("B" & lngRowCounter)
Loop

' if we don't find anything, return an empty string '
FindValue = ""


End Function

The above function takes in two range values, so you can use it like you would use any other function in Excel. Using the example you provided above, copy those cells into cells A2:C5. Next, in cell A1 put A. In cell B1 put 1. In C1, put =FindValue(A1,B1). This will execute the code above and return a match if it finds it.

Furthermore, if you change the "input values" from cells A1 or B1, your answer will update accordingly.

Answer 2

Another possibility using ADO:

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range

strFile = ActiveWorkbook.FullName

''Note HDR=No, so F1,F2 etc is used for column names
''If HDR=Yes, the names in the first row of the range
''can be used.
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set r1 = Worksheets("Sheet11").Range("F1")
Set r2 = Worksheets("Sheet11").Range("F2")
Set r3 = Worksheets("Sheet11").Range("F3")

cn.Open strCon

''Case sensitive, one text (f1), one numeric (f2) value
strSQL = "SELECT F3 FROM [Sheet11$A1:C4] WHERE F1='" & r1.Value _
       & "' AND F2=" & r2.Value

rs.Open strSQL, cn, 3, 3

''Copies all matches
r3.CopyFromRecordset rs

Answer 3

If he can put up with another colum to the left of the ones mentioned above (which you can hide from normal view), you can do it without using any VBA.

Insert a colum to the left of the first one, and set it to =A1&B1, =A2&B2 etc. You can then use VLOOKUP(x,A1:Dn,4) - where x is the string ("A1", "B2", etc.) that he wants to look up, and n is the number of rows in the dataset.

Hope that helps.