Search in a Matrix

[bleeding_me]

Hello my friends.

I hope you can help me here.

I have an excel with a matrix like the following

	      Abrantes	     Amiais 	       Benavente	Chamusca	      Constância
Abrantes 		                58	             98	    40	                   14
Alcanede 	66	                12	             67	    54         	   50
Alcanena 	46	                10	             72	    32	                   36
Almeirim 	66	                38	             34	    24	                   60

In this matrix you have the "origin city" as rows on the left, the "destination city" as columns , and the values.

What I need to do is, given a origin city and a destination city, the macro must read the value.

Anyone can help me how to achieve this ?

Thank you in advance,
Bleeding_me

[Andy Pope]

Assuming your table is in A1:F5 then one way would be a combination of INDEX and MATCH

=INDEX(B2:F5,MATCH(I3,A2:A5,0),MATCH(I4,B1:F1,0))

Where I3 contains Origin city and I4 destination city

[bleeding_me]

Hello Andy,

thank you for your reply but I try what u say and I get #N/A ...

This is what I put (I have excel 2007)

=INDEX(B2:F5;MATCH(I3;A2:A5;0);MATCH(I4;B1:F1;0))

[Andy Pope]

Did you enter the 2 cities to lookup?

See attached,

[bleeding_me]

Andy,

I'm sorry, it was my mistake in writing one of the cities...

This works like a charm ...by the way, is there any way of "converting" this excel formula in a piece of VBA code ?

[Marcol]

A bit behind the curve here, but the example was done so I'll post it.

This uses Dynamic Named Ranges with INDEX() & MATCH()
Expand the Matrix/Table as required.

[Andy Pope]

coded approach

Sub X()

    Dim lngRow As Long
    Dim lngCol As Long
    Dim rngMatrix As Range
    Dim strOriginCity As String
    Dim strDestinationCity As String
    
    Set rngMatrix = Range("A1:F5")
    strOriginCity = Range("I3")
    strDestinationCity = Range("I4")
    
    lngRow = Application.Match(strOriginCity, rngMatrix.Columns(1), 0)
    lngCol = Application.Match(strDestinationCity, rngMatrix.Rows(1), 0)
    MsgBox "Value =" & rngMatrix(lngRow, lngCol)
    
End Sub