Emily
I'm not sure which row you want to find. and MATCH will not find a true
value if the list is not sorted.
Say you wanted to know how many values in the list are equal to A1, then use
this function:
=SUMPRODUCT(--(A2:A400=A1))
You can then use this User Defined Function (UDF) to find the row of the
instance. For example my list contained three Cats. To find the row of the
second cat in the list you will enter:
=findrow(A1,A4:A400,2)
To find the last row type:
=findrow(A1,A4:A400)
To make this work you have to copy the following code into a Visual Basic
Module.
Press ALT + F11, Insert Module then Paste the code into the module, close
the module, File>Close and enter the formula in the worksheet. The code is
between the Lines
' ================================================
Option Explicit
Function FindRow(ByVal Ref As Range, Data As Range, Optional Instance As
Long) As Long
Dim c As Variant
Dim row As Long
Dim Counter As Long
Select Case Instance
Case Is = 0 ' or missing, find last row
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
FindRow = row
End If
Next
Case Is > 0 ' find the row of the Nth instance
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
Counter = Counter + 1
If Counter = Instance Then
FindRow = row
Exit Function
End If
End If
Next
End Select
End Function
' ============================================
Hope this give you what you want.
Peter
