Find match from 1st 3 columns and return the row number

[HelpMe]

I need help writing a function that would find a match in the first 3 columns
in a sheet. Then return the row number where the combination was found. Then
return the row number of the next non blank row it finds. Here is an example:

Row1 ColA ColB ColC
Row2 DDD EEE FFF
Row3
Row4 HHH

I would need to search for the row that contains DDD in ColA, if match then
look for EEE in ColB, and if match then look for FFF in ColC. Once found then
return the row number where found, in this case 2. Then find the next value
in ColA that is non blank and return this row number, in this case 4.

I really appreciate your help with this. God bless you all for all the help
you provide!

 

[Dave Peterson]

Maybe...

=match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.


Will return 1 if the first row of A4:C99 matches (row 4).
Will return 2 if the second row of A4:C99 matches (row 5).

So if you want to see 4 when it matches row 4, add 3 to the formula:

=3+match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0)
or
=row(a4:a99)-1+match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0)
(If you want to adjust the ranges)

 

[Joel]

Try this function

Function FindRowAfterMatch() As Long

With ActiveSheet
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
FoundMatch = False
For RowCount = 2 To LastRow
If FoundMatch = False Then
If .Range("A" & RowCount) = "DDD" And _
.Range("B" & RowCount) = "EEE" And _
.Range("C" & RowCount) = "FFF" Then

FoundMatch = True
End If
Else
If .Range("A" & RowCount) <> "" Then
FindRowAfterMatch = RowCount
Exit For
End If
End If

Next RowCount

End With

End Function

 

[HelpMe]

Thank you! This is very helpful but I have a couple of questions:

1- where is the row number of the match?
2- FindRowAfterMatch is the row number of the next non blank in col A?
3- What happens if combination found is the last row in rowcount, what would
be the value of FindRowAfterMatch?

Thank you very much for your help.

 

[HelpMe]

Thanks!

Maybe...

=match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.


Will return 1 if the first row of A4:C99 matches (row 4).
Will return 2 if the second row of A4:C99 matches (row 5).

So if you want to see 4 when it matches row 4, add 3 to the formula:

=3+match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0)
or
=row(a4:a99)-1+match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0)
(If you want to adjust the ranges)

 

[Joel]

The row number gets put into the return parameter of the function

FindRowAfterMatch = RowCount

Nothing will get returned if a match isn't found or the match is in the last
row. In that case FindRowAfterMatch will be null.

If you want something to be returned then the modification below wil return
-1 if nothing is found


Function FindRowAfterMatch() As Long

FindRowAfterMatch = -1

With ActiveSheet
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
FoundMatch = False
For RowCount = 2 To LastRow
If FoundMatch = False Then
If .Range("A" & RowCount) = "DDD" And _
.Range("B" & RowCount) = "EEE" And _
.Range("C" & RowCount) = "FFF" Then

FoundMatch = True
End If
Else
If .Range("A" & RowCount) <> "" Then
FindRowAfterMatch = RowCount
Exit For
End If
End If

Next RowCount

End With

End Function

 

[HelpMe]

Thanks! I modified it a bit to also get the Match Row Number . You are awesome.