software

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
From S100 thru X105:

xxx20012002200320042005
dogs12345
cats109876
pigs1112131415
fish2019181716
birds2122232425a 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 = quot;quot;
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 amp; Chr(10) amp; s2
End Function=titles(S100:X105,12) will display:quot;2002
pigsquot;

--
Gary's Studentquot;CLRquot; wrote:

gt; Hi All......
gt; I have a small 5x5 matrix on a worksheet. Normally one supplies the Row
gt; and Column Titles of a matrix to return the crossover value. I want to do it
gt; in reverse. I want to supply the crossover value and in return get the Row
gt; and Column Titles from the matrix, (not the Excel cell address).
gt;
gt; TIA for any assistance,
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;

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.

quot;CLRquot; wrote:

gt; Hi All......
gt; I have a small 5x5 matrix on a worksheet. Normally one supplies the Row
gt; and Column Titles of a matrix to return the crossover value. I want to do it
gt; in reverse. I want to supply the crossover value and in return get the Row
gt; and Column Titles from the matrix, (not the Excel cell address).
gt;
gt; TIA for any assistance,
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;

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.

quot;Miguel Zapicoquot; wrote:

gt; You can use matrix multiplication to get the result. Supposing your matrix
gt; is in B3:F7, and the value to you want to locate is in A10, you can use
gt; =MATCH(1,MMULT(--(B3:F7=A10),{1;1;1;1;1}),0) (gives the row, from 1 to 5)
gt; =MATCH(1,MMULT({1,1,1,1,1},--(B3:F7=A10)),0) (gives the column, from 1 to 5)
gt;
gt; You don't need to introduce them as array formulas, they just use arrays as
gt; arguments.
gt;
gt; Hope this helps,
gt; Miguel.
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; Hi All......
gt; gt; I have a small 5x5 matrix on a worksheet. Normally one supplies the Row
gt; gt; and Column Titles of a matrix to return the crossover value. I want to do it
gt; gt; in reverse. I want to supply the crossover value and in return get the Row
gt; gt; and Column Titles from the matrix, (not the Excel cell address).
gt; gt;
gt; gt; TIA for any assistance,
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;

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)gt;
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)gt;0,0),0),0))

....confirmed with CONTROL SHIFT ENTER

Hope this helps!

In article gt;,
CLR gt; wrote:

gt; Hi All......
gt; I have a small 5x5 matrix on a worksheet. Normally one supplies the Row
gt; and Column Titles of a matrix to return the crossover value. I want to do it
gt; in reverse. I want to supply the crossover value and in return get the Row
gt; and Column Titles from the matrix, (not the Excel cell address).
gt;
gt; TIA for any assistance,
gt; Vaya con Dios,
gt; Chuck, CABGx3

Thanks Miguel, that seems to work pretty good as you describe. But in this
instance, I want the result to be the quot;Titlesquot; from the Row and Column,
rather than it's relative number.Vaya con Dios,
Chuck, CABGx3
quot;Miguel Zapicoquot; wrote:

gt; You can use matrix multiplication to get the result. Supposing your matrix
gt; is in B3:F7, and the value to you want to locate is in A10, you can use
gt; =MATCH(1,MMULT(--(B3:F7=A10),{1;1;1;1;1}),0) (gives the row, from 1 to 5)
gt; =MATCH(1,MMULT({1,1,1,1,1},--(B3:F7=A10)),0) (gives the column, from 1 to 5)
gt;
gt; You don't need to introduce them as array formulas, they just use arrays as
gt; arguments.
gt;
gt; Hope this helps,
gt; Miguel.
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; Hi All......
gt; gt; I have a small 5x5 matrix on a worksheet. Normally one supplies the Row
gt; gt; and Column Titles of a matrix to return the crossover value. I want to do it
gt; gt; in reverse. I want to supply the crossover value and in return get the Row
gt; gt; and Column Titles from the matrix, (not the Excel cell address).
gt; gt;
gt; gt; TIA for any assistance,
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;

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.

quot;CLRquot; wrote:

gt; Thanks Miguel, that seems to work pretty good as you describe. But in this
gt; instance, I want the result to be the quot;Titlesquot; from the Row and Column,
gt; rather than it's relative number.
gt;
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;Miguel Zapicoquot; wrote:
gt;
gt; gt; You can use matrix multiplication to get the result. Supposing your matrix
gt; gt; is in B3:F7, and the value to you want to locate is in A10, you can use
gt; gt; =MATCH(1,MMULT(--(B3:F7=A10),{1;1;1;1;1}),0) (gives the row, from 1 to 5)
gt; gt; =MATCH(1,MMULT({1,1,1,1,1},--(B3:F7=A10)),0) (gives the column, from 1 to 5)
gt; gt;
gt; gt; You don't need to introduce them as array formulas, they just use arrays as
gt; gt; arguments.
gt; gt;
gt; gt; Hope this helps,
gt; gt; Miguel.
gt; gt;
gt; gt; quot;CLRquot; wrote:
gt; gt;
gt; gt; gt; Hi All......
gt; gt; gt; I have a small 5x5 matrix on a worksheet. Normally one supplies the Row
gt; gt; gt; and Column Titles of a matrix to return the crossover value. I want to do it
gt; gt; gt; in reverse. I want to supply the crossover value and in return get the Row
gt; gt; gt; and Column Titles from the matrix, (not the Excel cell address).
gt; gt; gt;
gt; gt; gt; TIA for any assistance,
gt; gt; gt; Vaya con Dios,
gt; gt; gt; Chuck, CABGx3
gt; gt; gt;
gt; gt; gt;

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, CABGx3quot;Miguel Zapicoquot; wrote:

gt; I realized later about the titles, and made another post with the INDEX
gt; function. I was too happy to have got the formula to run, that I forgot
gt; about the final part :-)
gt;
gt; Miguel.
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; Thanks Miguel, that seems to work pretty good as you describe. But in this
gt; gt; instance, I want the result to be the quot;Titlesquot; from the Row and Column,
gt; gt; rather than it's relative number.
gt; gt;
gt; gt;
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Miguel Zapicoquot; wrote:
gt; gt;
gt; gt; gt; You can use matrix multiplication to get the result. Supposing your matrix
gt; gt; gt; is in B3:F7, and the value to you want to locate is in A10, you can use
gt; gt; gt; =MATCH(1,MMULT(--(B3:F7=A10),{1;1;1;1;1}),0) (gives the row, from 1 to 5)
gt; gt; gt; =MATCH(1,MMULT({1,1,1,1,1},--(B3:F7=A10)),0) (gives the column, from 1 to 5)
gt; gt; gt;
gt; gt; gt; You don't need to introduce them as array formulas, they just use arrays as
gt; gt; gt; arguments.
gt; gt; gt;
gt; gt; gt; Hope this helps,
gt; gt; gt; Miguel.
gt; gt; gt;
gt; gt; gt; quot;CLRquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi All......
gt; gt; gt; gt; I have a small 5x5 matrix on a worksheet. Normally one supplies the Row
gt; gt; gt; gt; and Column Titles of a matrix to return the crossover value. I want to do it
gt; gt; gt; gt; in reverse. I want to supply the crossover value and in return get the Row
gt; gt; gt; gt; and Column Titles from the matrix, (not the Excel cell address).
gt; gt; gt; gt;
gt; gt; gt; gt; TIA for any assistance,
gt; gt; gt; gt; Vaya con Dios,
gt; gt; gt; gt; Chuck, CABGx3
gt; gt; gt; gt;
gt; gt; gt; gt;

Thanks very much Domenic........your formulas worked fine (after I got over
the email word-wrap thng....lol)

Vaya con Dios,
Chuck, CABGx3
quot;Domenicquot; wrote:

gt; Assumptions:
gt;
gt; B1:E1 contains the column labels
gt;
gt; A2:A5 contains the row labels
gt;
gt; B2:E5 contains the data
gt;
gt; G2 contains the 'crossover value' of interest
gt;
gt; Formulas:
gt;
gt; H2:
gt;
gt; =INDEX(A2:A5,MATCH(TRUE,COUNTIF(OFFSET(B2:E5,ROW(B 2:E5)-ROW(B2),0,1),G2)gt;
gt; 0,0))
gt;
gt; ....confirmed with CONTROL SHIFT ENTER
gt;
gt; I2:
gt;
gt; =INDEX(B1:E1,MATCH(G2,INDEX(B2:E5,MATCH(TRUE,COUNT IF(OFFSET(B2:E5,ROW(B2:
gt; E5)-ROW(B2),0,1),G2)gt;0,0),0),0))
gt;
gt; ....confirmed with CONTROL SHIFT ENTER
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; CLR gt; wrote:
gt;
gt; gt; Hi All......
gt; gt; I have a small 5x5 matrix on a worksheet. Normally one supplies the Row
gt; gt; and Column Titles of a matrix to return the crossover value. I want to do it
gt; gt; in reverse. I want to supply the crossover value and in return get the Row
gt; gt; and Column Titles from the matrix, (not the Excel cell address).
gt; gt;
gt; gt; TIA for any assistance,
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt;

Hi Garyquot;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, CABGx3quot;Gary''s Studentquot; wrote:

gt; From S100 thru X105:
gt;
gt; xxx20012002200320042005
gt; dogs12345
gt; cats109876
gt; pigs1112131415
gt; fish2019181716
gt; birds2122232425
gt;
gt;
gt; a 5x5 with titles
gt;
gt; Function titles(r As Range, v As Integer) As String
gt; Dim rr As Range, s1, s2 As String, gotit As Boolean
gt; titles = quot;quot;
gt; gotit = False
gt;
gt; For Each rr In r
gt; If rr.Value = v Then
gt; gotit = True
gt; Exit For
gt; End If
gt; Next
gt; If gotit = False Then Exit Function
gt;
gt; s1 = Cells(r.Row, rr.Column)
gt; s2 = Cells(rr.Row, r.Column)
gt; titles = s1 amp; Chr(10) amp; s2
gt; End Function
gt;
gt;
gt; =titles(S100:X105,12) will display:
gt;
gt;
gt; quot;2002
gt; pigsquot;
gt;
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; Hi All......
gt; gt; I have a small 5x5 matrix on a worksheet. Normally one supplies the Row
gt; gt; and Column Titles of a matrix to return the crossover value. I want to do it
gt; gt; in reverse. I want to supply the crossover value and in return get the Row
gt; gt; and Column Titles from the matrix, (not the Excel cell address).
gt; gt;
gt; gt; TIA for any assistance,
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;