| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search returning ans in terms of Rows & Columns
Hi,
Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
If your answer is in A2, then
=ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi Will ezt *rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
In C,1 format
=SUBSTITUTE(ADDRESS(1,MATCH($A$2,A1:F1,0),2,1),"$" ,",") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stefi" wrote in message ... If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi "Will" ezt rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
stefi,
wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi Will ezt *rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
That's another case!
I could solve it only with a UDF: Public Function FindInRng(findrng As Range, ansrng As Range) As String FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Address(False, False) End Function Usage (answer being in G1): =findinrng(A1:F2,$G$1) Adjust A1:F2 range to your needs! Regards, Stefi Will ezt *rta: stefi, wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi Will ezt *rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
If that UDF is gonna be used in a cell on a worksheet, then it won't work until
xl2002. ..Find doesn't work in xl2k and below if used in a function called from a cell on a worksheet. Stefi wrote: That's another case! I could solve it only with a UDF: Public Function FindInRng(findrng As Range, ansrng As Range) As String FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Address(False, False) End Function Usage (answer being in G1): =findinrng(A1:F2,$G$1) Adjust A1:F2 range to your needs! Regards, Stefi Will ezt *rta: stefi, wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi Will ezt *rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
Then it's up to Will either to use XL2003 or find somebody who can solve this
task with worksheet functions! Stefi Dave Peterson ezt *rta: If that UDF is gonna be used in a cell on a worksheet, then it won't work until xl2002. ..Find doesn't work in xl2k and below if used in a function called from a cell on a worksheet. Stefi wrote: That's another case! I could solve it only with a UDF: Public Function FindInRng(findrng As Range, ansrng As Range) As String FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Address(False, False) End Function Usage (answer being in G1): =findinrng(A1:F2,$G$1) Adjust A1:F2 range to your needs! Regards, Stefi âžWillâ ezt Ã*rta: stefi, wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi âžWillâ ezt Ã*rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| comparing columns and returning max value | Excel Discussion (Misc queries) | |||
| Search Columns & Rows | Excel Discussion (Misc queries) | |||
| searching a large database with a long list of search terms | Excel Discussion (Misc queries) | |||
| FIND or SEARCH Returning Erroneous #VALUE? | Excel Worksheet Functions | |||
| how to set up a vlookup table with 2 search terms? | Excel Worksheet Functions | |||
Hybrid Mode
