Formula Question

[John]

I have a spreadsheet with a column containing thousands of numbers. Is
there a formula whose results will list the cell reference (i.e., B29,
B1100, etc.) that contains a specified number? I know that I've seen
this before, but I can't find it again.

Thanks so much!

John

 

[duane]

there may ba a more simple way but if your list is a1:a7 and you ar
looking for the cell withthe value in b1

=CELL("address",OFFSET(A1,MATCH(B1,A1:A7,0)-1,0)

 

[John]

Duane,

Thanks very much! Your formula works fine if there is only one instance
of the number in cells A1:A7. But what if there is more than one
instance of the number in the cell range? Is a variation of your
formula capable of listing all of the cell references the number appears
in? Right now, the formula as you gave me lists the first cell it finds
the number in.

Thanks,
John

 

[Domenic]

Assuming that your numbers are in Column A, try the following array
formulas that need to be entered using CONTROL+SHIFT+ENTER...

C1, copied down:

=CELL("address",INDEX($A:$A,SMALL(IF($A$1:$A$7=$B$1,ROW($A$1:$A$7)),ROW()-ROW($C$1)+1)))

or

=IF(ROW()-ROW($C$1)+1<=COUNTIF($A$1:$A$7,$B$1),CELL("address",INDEX($A:$A,SMALL(IF($A$1:$A$7=$B$1,ROW($A$1:$A$7)),ROW()-ROW($C$1)+1))),"")

...where B1 contains your specified number.

Hope this helps!

 

[Biff]

Hi!

An alternative: CSE

=ADDRESS(SMALL(IF(B1=A$1:A$7,ROW(A$1:A$7)),ROW(1:1)),1)

....

=CELL("address",OFFSET(A1,MATCH(B1,A1:A7,0)-1,0))

....

That can be shortened to:

=ADDRESS(MATCH(B1,A$1:A$7,0),1)

Biff

 

[John]

Domenic.... Both formula's work great. Thank you so much for your kind
help.

John

 

[John]

Hi Biff,

Thanks for the help. I really appreciate it.

John