Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
Hi All......
I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
From S100 thru X105:
xxx 2001 2002 2003 2004 2005 dogs 1 2 3 4 5 cats 10 9 8 7 6 pigs 11 12 13 14 15 fish 20 19 18 17 16 birds 21 22 23 24 25 a 5x5 with titles Function titles(r As Range, v As Integer) As String Dim rr As Range, s1, s2 As String, gotit As Boolean titles = "" gotit = False For Each rr In r If rr.Value = v Then gotit = True Exit For End If Next If gotit = False Then Exit Function s1 = Cells(r.Row, rr.Column) s2 = Cells(rr.Row, r.Column) titles = s1 & Chr(10) & s2 End Function =titles(S100:X105,12) will display: "2002 pigs" -- Gary's Student "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
You can use matrix multiplication to get the result. Supposing your matrix
is in B3:F7, and the value to you want to locate is in A10, you can use =MATCH(1,MMULT(--(B3:F7=A10),{1;1;1;1;1}),0) (gives the row, from 1 to 5) =MATCH(1,MMULT({1,1,1,1,1},--(B3:F7=A10)),0) (gives the column, from 1 to 5) You don't need to introduce them as array formulas, they just use arrays as arguments. Hope this helps, Miguel. "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
Sorry I forgot the INDEX function (titles in B2:F2, A3:A7)
Row header: =INDEX(A3:A7,MATCH(1,MMULT(--(B3:F7=A10),{1;1;1;1;1}),0)) Column header: =INDEX(B2:F2,MATCH(1,MMULT({1,1,1,1,1},--(B3:F7=A10)),0)) Miguel. "Miguel Zapico" wrote: You can use matrix multiplication to get the result. Supposing your matrix is in B3:F7, and the value to you want to locate is in A10, you can use =MATCH(1,MMULT(--(B3:F7=A10),{1;1;1;1;1}),0) (gives the row, from 1 to 5) =MATCH(1,MMULT({1,1,1,1,1},--(B3:F7=A10)),0) (gives the column, from 1 to 5) You don't need to introduce them as array formulas, they just use arrays as arguments. Hope this helps, Miguel. "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
Assumptions:
B1:E1 contains the column labels A2:A5 contains the row labels B2:E5 contains the data G2 contains the 'crossover value' of interest Formulas: H2: =INDEX(A2:A5,MATCH(TRUE,COUNTIF(OFFSET(B2:E5,ROW(B 2:E5)-ROW(B2),0,1),G2) 0,0)) ....confirmed with CONTROL+SHIFT+ENTER I2: =INDEX(B1:E1,MATCH(G2,INDEX(B2:E5,MATCH(TRUE,COUNT IF(OFFSET(B2:E5,ROW(B2: E5)-ROW(B2),0,1),G2)0,0),0),0)) ....confirmed with CONTROL+SHIFT+ENTER Hope this helps! In article , CLR wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
Thanks Miguel, that seems to work pretty good as you describe. But in this
instance, I want the result to be the "Titles" from the Row and Column, rather than it's relative number. Vaya con Dios, Chuck, CABGx3 "Miguel Zapico" wrote: You can use matrix multiplication to get the result. Supposing your matrix is in B3:F7, and the value to you want to locate is in A10, you can use =MATCH(1,MMULT(--(B3:F7=A10),{1;1;1;1;1}),0) (gives the row, from 1 to 5) =MATCH(1,MMULT({1,1,1,1,1},--(B3:F7=A10)),0) (gives the column, from 1 to 5) You don't need to introduce them as array formulas, they just use arrays as arguments. Hope this helps, Miguel. "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
I realized later about the titles, and made another post with the INDEX
function. I was too happy to have got the formula to run, that I forgot about the final part :-) Miguel. "CLR" wrote: Thanks Miguel, that seems to work pretty good as you describe. But in this instance, I want the result to be the "Titles" from the Row and Column, rather than it's relative number. Vaya con Dios, Chuck, CABGx3 "Miguel Zapico" wrote: You can use matrix multiplication to get the result. Supposing your matrix is in B3:F7, and the value to you want to locate is in A10, you can use =MATCH(1,MMULT(--(B3:F7=A10),{1;1;1;1;1}),0) (gives the row, from 1 to 5) =MATCH(1,MMULT({1,1,1,1,1},--(B3:F7=A10)),0) (gives the column, from 1 to 5) You don't need to introduce them as array formulas, they just use arrays as arguments. Hope this helps, Miguel. "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
LOL....thanks again Miguel,
Your ammended formulas work just fine, even when I concatenated them to give the result in one cell...... Many thanks Vaya con Dios, Chuck, CABGx3 "Miguel Zapico" wrote: I realized later about the titles, and made another post with the INDEX function. I was too happy to have got the formula to run, that I forgot about the final part :-) Miguel. "CLR" wrote: Thanks Miguel, that seems to work pretty good as you describe. But in this instance, I want the result to be the "Titles" from the Row and Column, rather than it's relative number. Vaya con Dios, Chuck, CABGx3 "Miguel Zapico" wrote: You can use matrix multiplication to get the result. Supposing your matrix is in B3:F7, and the value to you want to locate is in A10, you can use =MATCH(1,MMULT(--(B3:F7=A10),{1;1;1;1;1}),0) (gives the row, from 1 to 5) =MATCH(1,MMULT({1,1,1,1,1},--(B3:F7=A10)),0) (gives the column, from 1 to 5) You don't need to introduce them as array formulas, they just use arrays as arguments. Hope this helps, Miguel. "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
Thanks very much Domenic........your formulas worked fine (after I got over
the email word-wrap thng....lol) Vaya con Dios, Chuck, CABGx3 "Domenic" wrote: Assumptions: B1:E1 contains the column labels A2:A5 contains the row labels B2:E5 contains the data G2 contains the 'crossover value' of interest Formulas: H2: =INDEX(A2:A5,MATCH(TRUE,COUNTIF(OFFSET(B2:E5,ROW(B 2:E5)-ROW(B2),0,1),G2) 0,0)) ....confirmed with CONTROL+SHIFT+ENTER I2: =INDEX(B1:E1,MATCH(G2,INDEX(B2:E5,MATCH(TRUE,COUNT IF(OFFSET(B2:E5,ROW(B2: E5)-ROW(B2),0,1),G2)0,0),0),0)) ....confirmed with CONTROL+SHIFT+ENTER Hope this helps! In article , CLR wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
Hi Gary"s Student, thanks for the reply. I had a little trouble with it at
first, getting some #VALUE! and #NAME errors.....but then I got the knack and it seems to be ok now. I really prefer this method over the other much longer formula suggestions, except that they both work on TEXT and I can't seem to make this one do it. It seems to fail when trying to look up a TEXT value, as well as any number in a row that has any TEXT in it and any row thereafter. I know that was not a requirement in the original problem, but could your solution be easily modified to work with both TEXT and numbers as well? If so, I would be much appreciative. Thanks again, Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote: From S100 thru X105: xxx 2001 2002 2003 2004 2005 dogs 1 2 3 4 5 cats 10 9 8 7 6 pigs 11 12 13 14 15 fish 20 19 18 17 16 birds 21 22 23 24 25 a 5x5 with titles Function titles(r As Range, v As Integer) As String Dim rr As Range, s1, s2 As String, gotit As Boolean titles = "" gotit = False For Each rr In r If rr.Value = v Then gotit = True Exit For End If Next If gotit = False Then Exit Function s1 = Cells(r.Row, rr.Column) s2 = Cells(rr.Row, r.Column) titles = s1 & Chr(10) & s2 End Function =titles(S100:X105,12) will display: "2002 pigs" -- Gary's Student "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
Hi CLR
I'll look at TEXT values later today. Right now the undergrads are swarming all over our help center. I'll be tied up until after 6:00PM (east coast). -- Gary's Student "CLR" wrote: Hi Gary"s Student, thanks for the reply. I had a little trouble with it at first, getting some #VALUE! and #NAME errors.....but then I got the knack and it seems to be ok now. I really prefer this method over the other much longer formula suggestions, except that they both work on TEXT and I can't seem to make this one do it. It seems to fail when trying to look up a TEXT value, as well as any number in a row that has any TEXT in it and any row thereafter. I know that was not a requirement in the original problem, but could your solution be easily modified to work with both TEXT and numbers as well? If so, I would be much appreciative. Thanks again, Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote: From S100 thru X105: xxx 2001 2002 2003 2004 2005 dogs 1 2 3 4 5 cats 10 9 8 7 6 pigs 11 12 13 14 15 fish 20 19 18 17 16 birds 21 22 23 24 25 a 5x5 with titles Function titles(r As Range, v As Integer) As String Dim rr As Range, s1, s2 As String, gotit As Boolean titles = "" gotit = False For Each rr In r If rr.Value = v Then gotit = True Exit For End If Next If gotit = False Then Exit Function s1 = Cells(r.Row, rr.Column) s2 = Cells(rr.Row, r.Column) titles = s1 & Chr(10) & s2 End Function =titles(S100:X105,12) will display: "2002 pigs" -- Gary's Student "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
You're very welcome, Chuck! Glad I could help! Also, I should have
added that if the 'crossover value' of interest occurs more than once in the data, the results will be based on the first occurrence. For example... 1) If the crossover value of interest is found in B4 and E3, the result will be based on E3. 2) If the crossover value of interest is found in C1 and C4, the result will be based on C1. 3) If the crossover value of interest is found in C4 and E4, the result will be based on C4. Hope this helps! In article , CLR wrote: Thanks very much Domenic........your formulas worked fine (after I got over the email word-wrap thng....lol) Vaya con Dios, Chuck, CABGx3 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
Thanks much Gary"s Student.........no great rush. I am wanting to use this
for an upcoming project.....plenty of time. Also, just discovered it don't seem to work in XL97......could something be changed to allow?................I really appreciate your help. Thanks again, Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote: Hi CLR I'll look at TEXT values later today. Right now the undergrads are swarming all over our help center. I'll be tied up until after 6:00PM (east coast). -- Gary's Student "CLR" wrote: Hi Gary"s Student, thanks for the reply. I had a little trouble with it at first, getting some #VALUE! and #NAME errors.....but then I got the knack and it seems to be ok now. I really prefer this method over the other much longer formula suggestions, except that they both work on TEXT and I can't seem to make this one do it. It seems to fail when trying to look up a TEXT value, as well as any number in a row that has any TEXT in it and any row thereafter. I know that was not a requirement in the original problem, but could your solution be easily modified to work with both TEXT and numbers as well? If so, I would be much appreciative. Thanks again, Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote: From S100 thru X105: xxx 2001 2002 2003 2004 2005 dogs 1 2 3 4 5 cats 10 9 8 7 6 pigs 11 12 13 14 15 fish 20 19 18 17 16 birds 21 22 23 24 25 a 5x5 with titles Function titles(r As Range, v As Integer) As String Dim rr As Range, s1, s2 As String, gotit As Boolean titles = "" gotit = False For Each rr In r If rr.Value = v Then gotit = True Exit For End If Next If gotit = False Then Exit Function s1 = Cells(r.Row, rr.Column) s2 = Cells(rr.Row, r.Column) titles = s1 & Chr(10) & s2 End Function =titles(S100:X105,12) will display: "2002 pigs" -- Gary's Student "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
"Gotcha" Domenic........thanks for the clarification.
Vaya con Dios, Chuck, CABGx3 "Domenic" wrote: You're very welcome, Chuck! Glad I could help! Also, I should have added that if the 'crossover value' of interest occurs more than once in the data, the results will be based on the first occurrence. For example... 1) If the crossover value of interest is found in B4 and E3, the result will be based on E3. 2) If the crossover value of interest is found in C1 and C4, the result will be based on C1. 3) If the crossover value of interest is found in C4 and E4, the result will be based on C4. Hope this helps! In article , CLR wrote: Thanks very much Domenic........your formulas worked fine (after I got over the email word-wrap thng....lol) Vaya con Dios, Chuck, CABGx3 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
New code:
Function titles(r As Range, vv As Range) As String Dim rr As Range, s1, s2 As String, gotit As Boolean titles = "" gotit = False v = vv.Value ' NEW LINE For Each rr In r If rr.Value = v Then gotit = True Exit For End If Next If gotit = False Then Exit Function s1 = Cells(r.Row, rr.Column) s2 = Cells(rr.Row, r.Column) titles = s1 & Chr(10) & s2 End Function 1. changed header from integer to range 2. added a line to get value from range 3. tested it on: xxx 2001 2002 2003 2004 2005 dogs 1 2 3 4 5 cats 10 9 8 7 6 pigs 11 12 13 14 15 fish 20 19 carp 17 16 birds 21 22 23 24 25 in S100 thru X105. This version works on numbers, text, and dates. There is a slight (better) difference in usage. For example in P88 enter 17 or carp. Then use as =titles(S100:X105,P88) so we refer to a cell rather than putting the value in the formula. I no longer have access to a machine with Excel97 on it. I looked at the code and its plain vanilla. Should work on 97; just can't test it. Have a pleasant weekend. -- Gary's Student "CLR" wrote: Thanks much Gary"s Student.........no great rush. I am wanting to use this for an upcoming project.....plenty of time. Also, just discovered it don't seem to work in XL97......could something be changed to allow?................I really appreciate your help. Thanks again, Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote: Hi CLR I'll look at TEXT values later today. Right now the undergrads are swarming all over our help center. I'll be tied up until after 6:00PM (east coast). -- Gary's Student "CLR" wrote: Hi Gary"s Student, thanks for the reply. I had a little trouble with it at first, getting some #VALUE! and #NAME errors.....but then I got the knack and it seems to be ok now. I really prefer this method over the other much longer formula suggestions, except that they both work on TEXT and I can't seem to make this one do it. It seems to fail when trying to look up a TEXT value, as well as any number in a row that has any TEXT in it and any row thereafter. I know that was not a requirement in the original problem, but could your solution be easily modified to work with both TEXT and numbers as well? If so, I would be much appreciative. Thanks again, Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote: From S100 thru X105: xxx 2001 2002 2003 2004 2005 dogs 1 2 3 4 5 cats 10 9 8 7 6 pigs 11 12 13 14 15 fish 20 19 18 17 16 birds 21 22 23 24 25 a 5x5 with titles Function titles(r As Range, v As Integer) As String Dim rr As Range, s1, s2 As String, gotit As Boolean titles = "" gotit = False For Each rr In r If rr.Value = v Then gotit = True Exit For End If Next If gotit = False Then Exit Function s1 = Cells(r.Row, rr.Column) s2 = Cells(rr.Row, r.Column) titles = s1 & Chr(10) & s2 End Function =titles(S100:X105,12) will display: "2002 pigs" -- Gary's Student "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
Well Gary"s Student, that new code of yours is simply
OUTSTANDING!!!!!.....When I grow up I want to be able to write code "jus like you"............seriously, I do really appreciate your kindness, and considerable talent put forth to solve this problem for me. Thank you most kindly. This new version runs equally well on my XL2k and XL97SR1 I have here at home. Whereas the first one did not run on the XL97SR2 I had at work, nor will it run on XL97SR1 here at home. I dunno what the difference is, but this new one really does good on both versions. Even tho I also have XP at work, I really need it to be '97 compatible, because most of my users only have 97, and it's like pulling teeth to get the MIS dept to upgrade them. Incidently, this also works..... =titles(INDIRECT(J1),MyLookup) with "S100:X105" in J1 and P88 named "MyLookup", but I have been unable to get a RangeName to specify the matrix in the formula.......... But that's "SmallStuff", the main thing works and for that I thank you again and again. Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote in message ... New code: Function titles(r As Range, vv As Range) As String Dim rr As Range, s1, s2 As String, gotit As Boolean titles = "" gotit = False v = vv.Value ' NEW LINE For Each rr In r If rr.Value = v Then gotit = True Exit For End If Next If gotit = False Then Exit Function s1 = Cells(r.Row, rr.Column) s2 = Cells(rr.Row, r.Column) titles = s1 & Chr(10) & s2 End Function 1. changed header from integer to range 2. added a line to get value from range 3. tested it on: xxx 2001 2002 2003 2004 2005 dogs 1 2 3 4 5 cats 10 9 8 7 6 pigs 11 12 13 14 15 fish 20 19 carp 17 16 birds 21 22 23 24 25 in S100 thru X105. This version works on numbers, text, and dates. There is a slight (better) difference in usage. For example in P88 enter 17 or carp. Then use as =titles(S100:X105,P88) so we refer to a cell rather than putting the value in the formula. I no longer have access to a machine with Excel97 on it. I looked at the code and its plain vanilla. Should work on 97; just can't test it. Have a pleasant weekend. -- Gary's Student "CLR" wrote: Thanks much Gary"s Student.........no great rush. I am wanting to use this for an upcoming project.....plenty of time. Also, just discovered it don't seem to work in XL97......could something be changed to allow?................I really appreciate your help. Thanks again, Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote: Hi CLR I'll look at TEXT values later today. Right now the undergrads are swarming all over our help center. I'll be tied up until after 6:00PM (east coast). -- Gary's Student "CLR" wrote: Hi Gary"s Student, thanks for the reply. I had a little trouble with it at first, getting some #VALUE! and #NAME errors.....but then I got the knack and it seems to be ok now. I really prefer this method over the other much longer formula suggestions, except that they both work on TEXT and I can't seem to make this one do it. It seems to fail when trying to look up a TEXT value, as well as any number in a row that has any TEXT in it and any row thereafter. I know that was not a requirement in the original problem, but could your solution be easily modified to work with both TEXT and numbers as well? If so, I would be much appreciative. Thanks again, Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote: From S100 thru X105: xxx 2001 2002 2003 2004 2005 dogs 1 2 3 4 5 cats 10 9 8 7 6 pigs 11 12 13 14 15 fish 20 19 18 17 16 birds 21 22 23 24 25 a 5x5 with titles Function titles(r As Range, v As Integer) As String Dim rr As Range, s1, s2 As String, gotit As Boolean titles = "" gotit = False For Each rr In r If rr.Value = v Then gotit = True Exit For End If Next If gotit = False Then Exit Function s1 = Cells(r.Row, rr.Column) s2 = Cells(rr.Row, r.Column) titles = s1 & Chr(10) & s2 End Function =titles(S100:X105,12) will display: "2002 pigs" -- Gary's Student "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Matrix lookup?
You are very welcome.
-- Gary''s Student "CLR" wrote: Well Gary"s Student, that new code of yours is simply OUTSTANDING!!!!!.....When I grow up I want to be able to write code "jus like you"............seriously, I do really appreciate your kindness, and considerable talent put forth to solve this problem for me. Thank you most kindly. This new version runs equally well on my XL2k and XL97SR1 I have here at home. Whereas the first one did not run on the XL97SR2 I had at work, nor will it run on XL97SR1 here at home. I dunno what the difference is, but this new one really does good on both versions. Even tho I also have XP at work, I really need it to be '97 compatible, because most of my users only have 97, and it's like pulling teeth to get the MIS dept to upgrade them. Incidently, this also works..... =titles(INDIRECT(J1),MyLookup) with "S100:X105" in J1 and P88 named "MyLookup", but I have been unable to get a RangeName to specify the matrix in the formula.......... But that's "SmallStuff", the main thing works and for that I thank you again and again. Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote in message ... New code: Function titles(r As Range, vv As Range) As String Dim rr As Range, s1, s2 As String, gotit As Boolean titles = "" gotit = False v = vv.Value ' NEW LINE For Each rr In r If rr.Value = v Then gotit = True Exit For End If Next If gotit = False Then Exit Function s1 = Cells(r.Row, rr.Column) s2 = Cells(rr.Row, r.Column) titles = s1 & Chr(10) & s2 End Function 1. changed header from integer to range 2. added a line to get value from range 3. tested it on: xxx 2001 2002 2003 2004 2005 dogs 1 2 3 4 5 cats 10 9 8 7 6 pigs 11 12 13 14 15 fish 20 19 carp 17 16 birds 21 22 23 24 25 in S100 thru X105. This version works on numbers, text, and dates. There is a slight (better) difference in usage. For example in P88 enter 17 or carp. Then use as =titles(S100:X105,P88) so we refer to a cell rather than putting the value in the formula. I no longer have access to a machine with Excel97 on it. I looked at the code and its plain vanilla. Should work on 97; just can't test it. Have a pleasant weekend. -- Gary's Student "CLR" wrote: Thanks much Gary"s Student.........no great rush. I am wanting to use this for an upcoming project.....plenty of time. Also, just discovered it don't seem to work in XL97......could something be changed to allow?................I really appreciate your help. Thanks again, Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote: Hi CLR I'll look at TEXT values later today. Right now the undergrads are swarming all over our help center. I'll be tied up until after 6:00PM (east coast). -- Gary's Student "CLR" wrote: Hi Gary"s Student, thanks for the reply. I had a little trouble with it at first, getting some #VALUE! and #NAME errors.....but then I got the knack and it seems to be ok now. I really prefer this method over the other much longer formula suggestions, except that they both work on TEXT and I can't seem to make this one do it. It seems to fail when trying to look up a TEXT value, as well as any number in a row that has any TEXT in it and any row thereafter. I know that was not a requirement in the original problem, but could your solution be easily modified to work with both TEXT and numbers as well? If so, I would be much appreciative. Thanks again, Vaya con Dios, Chuck, CABGx3 "Gary''s Student" wrote: From S100 thru X105: xxx 2001 2002 2003 2004 2005 dogs 1 2 3 4 5 cats 10 9 8 7 6 pigs 11 12 13 14 15 fish 20 19 18 17 16 birds 21 22 23 24 25 a 5x5 with titles Function titles(r As Range, v As Integer) As String Dim rr As Range, s1, s2 As String, gotit As Boolean titles = "" gotit = False For Each rr In r If rr.Value = v Then gotit = True Exit For End If Next If gotit = False Then Exit Function s1 = Cells(r.Row, rr.Column) s2 = Cells(rr.Row, r.Column) titles = s1 & Chr(10) & s2 End Function =titles(S100:X105,12) will display: "2002 pigs" -- Gary's Student "CLR" wrote: Hi All...... I have a small 5x5 matrix on a worksheet. Normally one supplies the Row and Column Titles of a matrix to return the crossover value. I want to do it in reverse. I want to supply the crossover value and in return get the Row and Column Titles from the matrix, (not the Excel cell address). TIA for any assistance, Vaya con Dios, Chuck, CABGx3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reverse mapping for LOOKUP table? | Excel Worksheet Functions | |||
lookup in MATRIX | Excel Worksheet Functions | |||
Need help with reverse phone lookup | Excel Worksheet Functions | |||
Lookup in Matrix | Excel Worksheet Functions | |||
Matrix Query Part II - lookup value | Excel Worksheet Functions |