#1   Report Post  
John
 
Posts: n/a
Default Formula Question

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

  #2   Report Post  
duane
 
Posts: n/a
Default


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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=277934

  #3   Report Post  
John
 
Posts: n/a
Default

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



  #4   Report Post  
Domenic
 
Posts: n/a
Default


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...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=277934

  #5   Report Post  
Biff
 
Posts: n/a
Default

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

.



  #6   Report Post  
John
 
Posts: n/a
Default

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

John

Domenic wrote:
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))






  #7   Report Post  
John
 
Posts: n/a
Default

Hi Biff,

Thanks for the help. I really appreciate it.

John

Biff wrote:
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

.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 02:49 PM
Formula Question Qaspec Excel Discussion (Misc queries) 2 January 10th 05 04:59 AM
Formula Question.....PLEASE PLEASE help! Anant Excel Discussion (Misc queries) 4 January 7th 05 10:30 AM
SUM array formula question Dan Excel Worksheet Functions 6 November 8th 04 06:49 AM
Excel Formula Question Joyce Excel Worksheet Functions 2 November 2nd 04 10:37 PM


All times are GMT +1. The time now is 10:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"