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, FileClose 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
"Emily" wrote:

it is likely that there will be many instances of the value the actual value
of that cell A1 returns is not of interest.


"Billy Liddel" wrote:

=MATCH(A1,A2:A400,0)+1

Assumes that there is only one instance of CAT

HTH
Peter

"Emily" wrote:

I have a spreadsheet that has many cells linked to other cells on the same
sheet
From the links I want to know the row number of the reference.
Eg:
A1 value CAT
A200 value CAT

Cell A1 has the formula "=A200"

Using Cell A1 information only I want to know row the row number it
references , that is 200. How do i do this?