| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a value in a table and return the cell reference
Would like to discuss a little bit about this function.
ROW(A1)-1 will always be zero. Why it is necessary to include this phrase? The role of MIN function is to return a figure from an array of only this figure and other "False". So is it possible to use MAX function, instead of MIN? PLease give some hint in this regard. "Domenic" wrote in message ... Try... =SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MATCH(J2,INDEX(A1:C3,MIN(IF(A1:C3=J2,ROW(A1:C 3)-ROW(A1)+1)),0),0))), "$","") ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, since the table contains unique values, the following should suffice... =SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MIN(IF(A1:C3=J2,COLUMN(A1:C3)-COLUMN(A1)+1)))),"$","") ...which also needs to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , mpaino wrote: Hi All, is it possible to create a function that will look for a text/value in a table and return the cell reference? I only have dierent values in the table, no value repeats. For Example: table A1:C3 aaa bbb ccc abb abc acb baa bba bbc in another cell (J2) I have the value "abc" and I want a formula to return the cell reference in the spreadsheet nest to it. I don't want the "abc" in the formula because I want to find different values. function(J2) returns the cell reference in the spreadsheet, B2. Thanks a lot for the help |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value in a table and return the cell reference
Hi All,
is it possible to create a function that will look for a text/value in a table and return the cell reference? I only have dierent values in the table, no value repeats. For Example: table A1:C3 aaa bbb ccc abb abc acb baa bba bbc in another cell (J2) I have the value "abc" and I want a formula to return the cell reference in the spreadsheet nest to it. I don't want the "abc" in the formula because I want to find different values. function(J2) returns the cell reference in the spreadsheet, B2. Thanks a lot for the help |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value in a table and return the cell reference
Try...
=SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MATCH(J2,INDEX(A1:C3,MIN(IF(A1:C3=J2,ROW(A1:C 3)-ROW(A1)+1)),0),0))), "$","") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, since the table contains unique values, the following should suffice... =SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MIN(IF(A1:C3=J2,COLUMN(A1:C3)-COLUMN(A1)+1)))),"$","") ....which also needs to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , mpaino wrote: Hi All, is it possible to create a function that will look for a text/value in a table and return the cell reference? I only have dierent values in the table, no value repeats. For Example: table A1:C3 aaa bbb ccc abb abc acb baa bba bbc in another cell (J2) I have the value "abc" and I want a formula to return the cell reference in the spreadsheet nest to it. I don't want the "abc" in the formula because I want to find different values. function(J2) returns the cell reference in the spreadsheet, B2. Thanks a lot for the help |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value in a table and return the cell reference
You can give this a try. This is a UDF so the code must be placed in a
standard code module (not a sheet or ThisWorkbook). Public Function MyFunction(ByVal FindValue As Variant, _ SearchRange As Range) As Range on error resume next Set MyFunction = SearchRange.Find(What:=FindValue, _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=False) on error Goto 0 End Function It returns the cell that contains the value you are looking for... You can use it in a cell like this... =MyFunction("abc", A1:C3) One note is that whiel the function returns the cell where the value was found the result will just be abc since that is what that cell contains so I am not to sure how this help you... -- HTH... Jim Thomlinson "mpaino" wrote: Hi All, is it possible to create a function that will look for a text/value in a table and return the cell reference? I only have dierent values in the table, no value repeats. For Example: table A1:C3 aaa bbb ccc abb abc acb baa bba bbc in another cell (J2) I have the value "abc" and I want a formula to return the cell reference in the spreadsheet nest to it. I don't want the "abc" in the formula because I want to find different values. function(J2) returns the cell reference in the spreadsheet, B2. Thanks a lot for the help |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value in a table and return the cell reference
It works very well, thanks a lot for the help, but in the first formula I
figured out by the second that the last "row" has to be replaced by "column". "Domenic" wrote: Try... =SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MATCH(J2,INDEX(A1:C3,MIN(IF(A1:C3=J2,ROW(A1:C 3)-ROW(A1)+1)),0),0))), "$","") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, since the table contains unique values, the following should suffice... =SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MIN(IF(A1:C3=J2,COLUMN(A1:C3)-COLUMN(A1)+1)))),"$","") ....which also needs to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , mpaino wrote: Hi All, is it possible to create a function that will look for a text/value in a table and return the cell reference? I only have dierent values in the table, no value repeats. For Example: table A1:C3 aaa bbb ccc abb abc acb baa bba bbc in another cell (J2) I have the value "abc" and I want a formula to return the cell reference in the spreadsheet nest to it. I don't want the "abc" in the formula because I want to find different values. function(J2) returns the cell reference in the spreadsheet, B2. Thanks a lot for the help |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value in a table and return the cell reference
Actually, that shouldn't be the case. Did the formula return an
incorrect value? In article , mpaino wrote: It works very well, thanks a lot for the help, but in the first formula I figured out by the second that the last "row" has to be replaced by "column". "Domenic" wrote: Try... =SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MATCH(J2,INDEX(A1:C3,MIN(IF(A1:C3=J2,ROW(A1:C 3)-ROW(A1)+1)),0),0))), "$","") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, since the table contains unique values, the following should suffice... =SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MIN(IF(A1:C3=J2,COLUMN(A1:C3)-COLUMN(A1)+1)))),"$","") ....which also needs to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , mpaino wrote: Hi All, is it possible to create a function that will look for a text/value in a table and return the cell reference? I only have dierent values in the table, no value repeats. For Example: table A1:C3 aaa bbb ccc abb abc acb baa bba bbc in another cell (J2) I have the value "abc" and I want a formula to return the cell reference in the spreadsheet nest to it. I don't want the "abc" in the formula because I want to find different values. function(J2) returns the cell reference in the spreadsheet, B2. Thanks a lot for the help |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value in a table and return the cell reference
=ADDRESS(SUMPRODUCT((A1:C3=J1)*(ROW(A1:C3))),SUMPR ODUCT((A1:C3=J1)*COLUMN(A1:C3)))
HTH Kostis Vezerides mpaino wrote: Hi All, is it possible to create a function that will look for a text/value in a table and return the cell reference? I only have dierent values in the table, no value repeats. For Example: table A1:C3 aaa bbb ccc abb abc acb baa bba bbc in another cell (J2) I have the value "abc" and I want a formula to return the cell reference in the spreadsheet nest to it. I don't want the "abc" in the formula because I want to find different values. function(J2) returns the cell reference in the spreadsheet, B2. Thanks a lot for the help |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value in a table and return the cell reference
The following modification will return the row and column numbers of the
specified value; e.g., 2, 2 Public Function MyFunction(ByVal FindValue As Variant, _ SearchRange As Range) As String On Error Resume Next MyFunction = SearchRange.Find(What:=FindValue, _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=False).Row & ", " & _ SearchRange.Find(What:=FindValue, _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=False).Column On Error GoTo 0 End Function Alan Beban Jim Thomlinson wrote: You can give this a try. This is a UDF so the code must be placed in a standard code module (not a sheet or ThisWorkbook). Public Function MyFunction(ByVal FindValue As Variant, _ SearchRange As Range) As Range on error resume next Set MyFunction = SearchRange.Find(What:=FindValue, _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=False) on error Goto 0 End Function It returns the cell that contains the value you are looking for... You can use it in a cell like this... =MyFunction("abc", A1:C3) One note is that whiel the function returns the cell where the value was found the result will just be abc since that is what that cell contains so I am not to sure how this help you... |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value in a table and return the cell reference
In article ,
"Rasoul Khoshravan" wrote: Would like to discuss a little bit about this function. ROW(A1)-1 will always be zero. Why it is necessary to include this phrase? This part of the formula... MIN(IF(A1:C3=J2,ROW(A1:C3)-ROW(A1)+1)) ....returns the row number in which the criteria is found, relative to the first row of the reference. In this case, because the data starts in Row 1, this part -ROW(A1)+1 isn't needed. But it's needed if the data starts in a row other than the first one, or if one or more rows are inserted at row one. For example, if A2:C4 contains the data, ROW(A2:C4) returns the following array of numbers... 2 3 4 If ROW(A2:C4)-ROW(A2)+1 is used instead, the following array of numbers is returned... 1 2 3 So as you can see, the added part at the end is used to return an array of numbers starting with the number 1. The role of MIN function is to return a figure from an array of only this figure and other "False". So is it possible to use MAX function, instead of MIN? PLease give some hint in this regard. If the data contains unique values, then it doesn't matter which one is used. However, if Row 1 and Row 3 both contain the criteria, MIN will return Row 1, whereas MAX will return Row 3. Hope this helps! In article , "Rasoul Khoshravan" wrote: Would like to discuss a little bit about this function. ROW(A1)-1 will always be zero. Why it is necessary to include this phrase? The role of MIN function is to return a figure from an array of only this figure and other "False". So is it possible to use MAX function, instead of MIN? PLease give some hint in this regard. "Domenic" wrote in message ... Try... =SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MATCH(J2,INDEX(A1:C3,MIN(IF(A1:C3=J2,ROW(A1:C 3)-ROW(A1)+1)),0),0))), "$","") ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, since the table contains unique values, the following should suffice... =SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MIN(IF(A1:C3=J2,COLUMN(A1:C3)-COLUMN(A1)+1)))),"$","") ...which also needs to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , mpaino wrote: Hi All, is it possible to create a function that will look for a text/value in a table and return the cell reference? I only have dierent values in the table, no value repeats. For Example: table A1:C3 aaa bbb ccc abb abc acb baa bba bbc in another cell (J2) I have the value "abc" and I want a formula to return the cell reference in the spreadsheet nest to it. I don't want the "abc" in the formula because I want to find different values. function(J2) returns the cell reference in the spreadsheet, B2. Thanks a lot for the help |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value in a table and return the cell reference
Domenic, the formula returned an error with row instead of column, that's why
I tried this....anyway, the formula works pretty well, thanks again. "Domenic" wrote: Actually, that shouldn't be the case. Did the formula return an incorrect value? In article , mpaino wrote: It works very well, thanks a lot for the help, but in the first formula I figured out by the second that the last "row" has to be replaced by "column". "Domenic" wrote: Try... =SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MATCH(J2,INDEX(A1:C3,MIN(IF(A1:C3=J2,ROW(A1:C 3)-ROW(A1)+1)),0),0))), "$","") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, since the table contains unique values, the following should suffice... =SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1) +1)),MIN(IF(A1:C3=J2,COLUMN(A1:C3)-COLUMN(A1)+1)))),"$","") ....which also needs to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , mpaino wrote: Hi All, is it possible to create a function that will look for a text/value in a table and return the cell reference? I only have dierent values in the table, no value repeats. For Example: table A1:C3 aaa bbb ccc abb abc acb baa bba bbc in another cell (J2) I have the value "abc" and I want a formula to return the cell reference in the spreadsheet nest to it. I don't want the "abc" in the formula because I want to find different values. function(J2) returns the cell reference in the spreadsheet, B2. Thanks a lot for the help |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value in a table and return the cell reference
In article ,
mpaino wrote: Domenic, the formula returned an error with row instead of column, that's why I tried this....anyway, the formula works pretty well, thanks again. The formula I posted seems fine. It shouldn't be necessary to make any changes other than adjusting the ranges according to your data. Changing it as you've describe could return #N/A in some cases. If you'd like to explore this further, post the exact formula you're using. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| return cell reference from any column | Excel Worksheet Functions | |||
| Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
Linear Mode
