Finding all rows containing a number

I have one column by 5000 rows.
I need to find out the index positions of a certain number.
So let's say the number 123 is located in row 1000 and row 1400.
I want a formula to just let me know right away that 123 is located in
the 1000 and 1400 rows.

I'd like to keep from having to use a filter and manually type out the
row. I'd like it to be automatic to save me time.
Best would be if I could then put the index positions as DATA in other
cells without manually typing it in.

Thanks guys!


---
Message posted from http://www.ExcelForum.com/

You would need to copy down a formula like

=SMALL(IF($A$1:$A$5000=123,ROW($1:$5000)),ROW(1:1) )

entered with ctrl + shift & enter

copy down until you get an error



--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

"brazen234 " wrote in message
...
I have one column by 5000 rows.
I need to find out the index positions of a certain number.
So let's say the number 123 is located in row 1000 and row 1400.
I want a formula to just let me know right away that 123 is located in
the 1000 and 1400 rows.

I'd like to keep from having to use a filter and manually type out the
row. I'd like it to be automatic to save me time.
Best would be if I could then put the index positions as DATA in other
cells without manually typing it in.

Thanks guys!


---
Message posted from http://www.ExcelForum.com/

Hi
enter the following in B1 as array formula (and copy down)
=SMALL(IF($A$1:$A$5000=123,ROW($A$1:$A$5000)),ROW( 1:1))

--
Regards
Frank Kabel
Frankfurt, Germany


I have one column by 5000 rows.
I need to find out the index positions of a certain number.
So let's say the number 123 is located in row 1000 and row 1400.
I want a formula to just let me know right away that 123 is located
in
the 1000 and 1400 rows.

I'd like to keep from having to use a filter and manually type out
the
row. I'd like it to be automatic to save me time.
Best would be if I could then put the index positions as DATA in
other
cells without manually typing it in.

Thanks guys!


---
Message posted from http://www.ExcelForum.com/