Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Formula Question | Excel Discussion (Misc queries) | |||
Formula Question.....PLEASE PLEASE help! | Excel Discussion (Misc queries) | |||
SUM array formula question | Excel Worksheet Functions | |||
Excel Formula Question | Excel Worksheet Functions |