Excel vba - find line number where colum data (multiple articles)

Question

I need a function in VBA that finds the line number based on 2 where clauses.

Here is an excel example:

** ABCD** 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 line number (in this case line number 2 ), where B = "day1" and A = "id2".

Based on the row number, I need to additionally get the values ​​of other columns, i.e. C2, D2

Hope the question is clear.

Thanks!

Answer 1

When setting up the data, you can use the MATCH function to get the line number:

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

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

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

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

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

Alternatively, you can put all this into one formula:

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

And if you do not want to look at errors, you can use 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 include a VBA solution for every user request. This uses a search loop that is very efficient and flexible, and shows how to retrieve values ​​from other columns after a match is 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 

Answer 2

In VBA, you can perform brute force checks as follows:

 Public Sub Test() Dim message As String Dim row As Long row = Find("id1", "day2") message = "Not Found" If (row > 0) Then message = ActiveSheet.Cells(row, 3).Value End If MsgBox message End Sub Function Find(aVal As String, bVal As String) As Long Dim maxRow As Long Dim row As Long maxRow = Range("A65536").End(xlUp).row For row = 2 To maxRow Dim a As String a = ActiveSheet.Cells(row, 1).Value b = ActiveSheet.Cells(row, 2).Value If a = aVal And b = bVal Then Find = row Exit Function End If Next row Find = -1 End Function