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

-----Original Message-----

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!

John Wrote:
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

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

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




--
Domenic
----------------------------------------------------------
--------------
Domenic's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=10785
View this thread:
http://www.excelforum.com/showthread...hreadid=277934

.