Lookup in a matrix

[infasys]

I have the Following table

<TABLE style="WIDTH: 377pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=503><COLGROUP><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 41pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=55></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>7</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=21>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND: #948a54; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-pattern: black none" class=xl65>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>13</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>16</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>19</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-pattern: black none" class=xl65>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>14</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND: #00b0f0; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-pattern: black none" class=xl65>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>18</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>21</TD></TR></TBODY></TABLE>


I would like to be able to lookup for example 11 and return 4 and B. 11 could be anywhere in the matrix. I was thinking of Setting a RangeName and then Looking it up to return the Number and Letter..

 

[MickG]

Hi, Name your entire Matrix "MyRng".
Enter the value to find in "InputBox" the code will return the Required values in Cells "I1" & "J1"

[COLOR=navy]Sub[/COLOR] MG26May25
[COLOR=navy]Dim[/COLOR] Message, Title, Default, MyValue [COLOR=navy]As[/COLOR] Double
[COLOR=navy]Dim[/COLOR] CL [COLOR=navy]As[/COLOR] Range
Message = "Enter a value between 1 and 100"
Title = "Matrix Find"
Default = ""
MyValue = InputBox(Message, Title, Default)
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] CL [COLOR=navy]In[/COLOR] Range("MyRng")
        [COLOR=navy]If[/COLOR] CL.row > 1 And CL.Column > 1 [COLOR=navy]Then[/COLOR]
            [COLOR=navy]If[/COLOR] MyValue = CL [COLOR=navy]Then[/COLOR]
                Range("I1") = Cells(CL.row, 1)
                Range("J1") = Cells(1, CL.Column)
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] CL
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[infasys]

Hi thanks For the Reply,

Dose the named Range have to have the Row and Columns Included In its Difinition?

Cheers

Morty

 

[jim may]

Yes, MyRng RefersTo A1:H4

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		1	2	3	4	5	6	7	B	4
2	A	1	4	7	10	13	16	19
3	B	2	5	8	11	14	17	20
4	C	3	6	9	12	15	18	21
Sheet1

Excel 2007

 

[infasys]

Hi There,

I found this Public Function on another thread...

Public Function ARRAYFIND(target1 As Double, target2 As Range)
For Each cell In target2
If cell.Value = target1 Then
variable1 = cell.Row
variable2 = cell.Column
variable3 = Cells(variable1, variable2).Address(False, False)
End If
Next
ARRAYFIND = variable3
End Function


This works Nice However it returns the Cell Co ordinates, Not the headings of the Arrays.. Eg I Get R7 instead of 15B...

Is there any simple code I could add to this function to Provide the headings on the Range. IF i ensure that the First Row and Column are the headings?

 

[infasys]

Could I Add Some VBA Like this to the function to Return the Information that I need..


Variable4= Cells(CL.row, 1)
Variable5 = Cells(1, CL.Column)

 

[infasys]

I have tried a few different Syntax.. Still Can not get it ... Any Help Would be appreciated..

I want the Function to Return the value in the First Column and First Row. Not the Actual Excel Location.....




Public Function ARRAYFIND(target1 As Double, target2 As Range)
For Each Cell In target2
If Cell.Value = target1 Then
Variable1 = CellValue.Row(1)
Variable2 = CellValue.Column(1)

variable3 = Cells(Variable1, Variable2).Address(False, False)

End If
Next
ARRAYFIND = variable3
End Function

 

[MickG]

Try this:-

Function ARRAYFIND(target1 [COLOR=navy]As[/COLOR] Double, target2 [COLOR=navy]As[/COLOR] Range) [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] cell [COLOR=navy]As[/COLOR] Range
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] cell [COLOR=navy]In[/COLOR] target2
    [COLOR=navy]If[/COLOR] Not cell.row = 1 And Not cell.Column = 1 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] cell.value = target1 [COLOR=navy]Then[/COLOR]
    ARRAYFIND = Cells(cell.row, 1) & "/" & Cells(1, cell.Column)
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]End[/COLOR] Function

Regards Mick

 

[infasys]

Hi Mick Thanks But no Luck with that

 

[infasys]

This is the Public Funciton as it is At the moment..

Public Function ARRAYFIND(target1 As Double, target2 As Range)
For Each cell In target2

If cell.Value = target1 Then
Variable1 = cell.Row
Variable2 = cell.Column

variable3 = Cells(Variable1, Variable2).Address(False, False)

End If
Next

ARRAYFIND = variable3

End Function

Works Great Except as mentioned Above I Get the the Cell Reference of the Item that is Looked up..

For example as above...

Arrayfind(11,A1:H4) Returns "E3" (Cell Reference) I need it to Return "B4" (The Headings in the Range Reference)