Find row number

  • Thread starter Thread starter Martin Wheeler
  • Start date Start date
M

Martin Wheeler

xl2000
I need to list the row number of a value in cell CE7. The value is in the
range A7:A24. I want the row number to be displayed in cell CA7.
So if the value in CE7 is 14 and 14 is in A11 then "11" would be displayed
in cell CA7.
Any help would be greatly appreciated
Ta,
Martin
 
Hi Don,
I checked Match but is returns the position within a range. So if the number
is the third in a range then it returns "3". I want the row number instead.
So the number maybe the third in the range but on row 28 and I want 28
displayed.
I think the formula I want is going to be a combination of the vlookup and
Row functions.
Ta,
Martin
 
You didn't post your formula for comments but did you try
=MATCH(J4,C:C)
This will find the row of the number in cell j4 in column C
 
Hi Don,
I'm using Anders' formula - it works fine.
Thanks for your help.
Ta,
Martin
 
This formula entered with ctrl + shift & enter will return the row number
without having to offset
the startrow - 1 of the range

=MAX((A7:A24=CE7)*(ROWS(A7:A24)))

they will differ if there are more than one match when this formula will
return the last occurrence,
you can also use

=MIN(IF(A7:A24=CE7,ROWS(A7:A24)))

which will work like match in finding the first occurrence,
also entered with ctrl + shift & enter. One could also use Anders formula
and offset
for the start row as follows

=MATCH(CE7,A7:A24,0)+ROW(A7)-1

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Back
Top