Find row number

[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

 

[Don Guillett]

Have a look in HELP index for MATCH

 

[Anders S]

One way,

=MATCH(CE7,A7:A24,0)+6

HTH
Anders Silven

 

[Martin Wheeler]

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

 

[Don Guillett]

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

 

[Martin Wheeler]

Hi Anders,
Thanks that's got it! It works a treat.
Ta,
Martin

 

[Martin Wheeler]

Hi Don,
I'm using Anders' formula - it works fine.
Thanks for your help.
Ta,
Martin

 

[Anders S]

Glad it helped. Thanks for the feedback.

Anders Silven

 

[Peo Sjoblom]

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

 

[Aladin Akyurek]

For robustness...

=MATCH(CE7,A7:A24,0)+CELL("Row",A7)-1