Pulling a single number from a matrix 1

[dreadnaught]

I have a matrix with is 5 lines and 5 rows and I want to be able to select a specific combination of that. How would I write the formula. Here is the example
X
a b c d e
1 10 15 20 30 40
2 20 21 22 23 24
X 3 30 32 34 33 38
4 40 25 35 67 34
5 50 12 14 56 89

In the case above I want to pull the number where the two "X's" appear in this case "34". this way a person can select what they want. Any hope would be greatly appresiated.

 

[SkipVought]

Hi,

[tt]
=INDEX(B2:F6,MATCH("X",A2:A6,0),MATCH("X",B1:F1,0))
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[dreadnaught]

Thanks it worked perfectly. I am so happy there are experts out here like you.

Dreadnaught

 

[SkipVought]

To spiff it up a bit...

Name the range B1:F1. rng1
Name the range A2:A6. rng2

Past this code into the SHEET OBJECT CODE WINDOW (right click the sheet tab and select View Code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   With Target
      Set r1 = Application.Intersect(Target, Range("rng1"))
      Set r2 = Application.Intersect(Target, Range("rng2"))
      If Not r1 Is Nothing Then
         Range("rng1").ClearContents
         .Value = "X"
      End If
      If Not r2 Is Nothing Then
         Range("rng2").ClearContents
         .Value = "X"
      End If
   End With
End Sub

Making a SELECTION in the rng1 area or rng2 area will "move" the X marker.




Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244