Formula Question

J

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
 
D

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

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
 
D

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!
 
B

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
 
Top