Finding all rows containing a number

B

brazen234

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 i
the 1000 and 1400 rows.

I'd like to keep from having to use a filter and manually type out th
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 othe
cells without manually typing it in.

Thanks guys
 
P

Peo Sjoblom

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
 
F

Frank Kabel

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))
 
Top