CODEWITHSUNDEEP
vbaexcelfind
Majid Javanmard
Majid Javanmard

Reputation: 127

Excel VBA: Way to find the value of a cell with multiple criteria

I have a large file that contain 3 columns and 23393 rows in a sheet. columns A refer to date, column B refer to integer numbers and the third column refer to value. I want to find the value of a cell (in third column) with multiple criteria.

1)the first criteria is a date (it changes from 1/1/2008 to 12/31/2009)

2)the second criteria is an integer number(it changes from 1 to 32)

Thanks

Upvotes: 4

Views: 8492

Answers (2)

carlossierra
carlossierra

Reputation: 4727

What about the simple approach? 

Function find(ByVal criteria1 As Date, ByVal criteria2 As Integer) As Variant

    For i = 2 To 300001
        If Cells(i, 1).Value = criteria1 Then
            If Cells(i, 2).Value = criteria2 Then
                find = Cells(i, 3).Value
                Exit Function
            End If
        End If
    Next i

    find = "N/A"

End Function

You can test it running a simple macro like this:

Sub storeFoundValues()

    Dim matches(2) As Variant
    Dim date1, date2 As Date
    Dim int1, int2 As Integer

    date1 = DateSerial(2015, 7, 15)
    int1 = 3
    matches(1) = find(date1, int1)
    Cells(1, 5) = matches(1) ' test the output

    date2 = DateSerial(2016, 1, 10)
    int2 = 2
    matches(2) = find(date2, int2)
    Cells(1, 6) = matches(2) ' test the output

End Sub

This took less than a second in a worksheet with 30.000 records.

enter image description here

Upvotes: 4

Gary's Student
Gary's Student

Reputation: 96791

For example:

=INDEX(C1:C20,SUMPRODUCT(--(A1:A20=DATEVALUE("6/17/2009"))*(B1:B20=25)*ROW(1:20)))

enter image description here

This approach assumes that only one row will match the criteria.

Upvotes: 1

Related Questions