Excel vba - find row number where colum data (multiple clauses)

excel, find, row, vba, where-clause

Solution

With your data setup like that, you can use the MATCH function to get the row number:

=MATCH(1,INDEX(($A$1:$A$6="id2")*($B$1:$B$6="day1"),),0)

If there are no matches for those criteria, the formula will return an #N/A error. You can also change the criteria to be cell references, for example:

=MATCH(1,INDEX(($A$1:$A$6=F1)*($B$1:$B$6=G1),),0)

For the second part of your question, returning values with the found row number, you can use the INDEX function to return a value from a column. So pretending the Match formula is in cell H1, these two formulas will return the value from column C and D respectively:

=INDEX($C$1:$C$6,H1)
=INDEX($D$1:$D$6,H1)

Alternately, you could put it all into a single formula:

=INDEX($C$1:$C$6,MATCH(1,INDEX(($A$1:$A$6=F1)*($B$1:$B$6=G1),),0))

And if you don't want to be looking at errors, you can use an IFERROR on excel 2007+

=IFERROR(INDEX($C$1:$C$6,MATCH(1,INDEX(($A$1:$A$6=F1)*($B$1:$B$6=G1),),0)),"No Matches")

Error checking for Excel 2003 and below:

=IF(ISNA(MATCH(1,INDEX(($A$1:$A$6=F1)*($B$1:$B$6=G1),),0)),"No Matches",INDEX($C$1:$C$6,MATCH(1,INDEX(($A$1:$A$6=F1)*($B$1:$B$6=G1),),0)))

[EDIT]: I am including a VBA solution per user request. This uses a find loop, which is very efficient and flexible, and shows how to extract values from other columns once a match has been found:

Sub tgr()

    Dim rngFound As Range
    Dim strFirst As String
    Dim strID As String
    Dim strDay As String

    strID = "id2"
    strDay = "day1"

    Set rngFound = Columns("A").Find(strID, Cells(Rows.Count, "A"), xlValues, xlWhole)
    If Not rngFound Is Nothing Then
        strFirst = rngFound.Address
        Do
            If LCase(Cells(rngFound.Row, "B").Text) = LCase(strDay) Then
                'Found a match
                MsgBox "Found a match at: " & rngFound.Row & Chr(10) & _
                       "Value in column C: " & Cells(rngFound.Row, "C").Text & Chr(10) & _
                       "Value in column D: " & Cells(rngFound.Row, "D").Text
            End If
            Set rngFound = Columns("A").Find(strID, rngFound, xlValues, xlWhole)
        Loop While rngFound.Address <> strFirst
    End If

    Set rngFound = Nothing

End Sub

Problem

I need a function in VBA that finds the row number based on 2 where clauses. Here is the Excel sample: ``` ** A B C D** 1 id1 day1 val1 xxx 2 id2 day1 val2 xxx 3 id3 day1 val3 xxx 4 id1 day2 val1 xxx 5 id2 day2 val2 xxx 6 id3 day2 val3 xxx ``` I need to find the row number (in this case row number is 2) where B = "day1" and A = "id2". Based on the row number, I need to further get the values of other columns, i.e. C2, D2 Hope that the question is clear. Thank you!

Original source