Using Index and Match functions to lookup data in another worksheet

[Chris]

Good Afternoon to all:

I am in the process of creating a schedule to determine the distances
between ports this information will then help to determine work start
times and other relevant information. For example the data matrix
sheet will contain the various ports and distances in a matrix format.
The schedule sheet will contain information such as:

Port Distance
Cell (A2)Savannah
Cell (A3)Rio de Janiero

the distance cell (lets call it B2) will refer back to A2 and A3 and
then lookup the distances from the data matrix sheet. I was trying to
use the following function but had some challenges with it. Below is
an example:

=Index(Matrix!$A$2:$A$31,MATCH(A2,$D$1:$AF$31,TRUE,0),MATCH(A3,$D$1:$AF$31,TRUE,0))

I am trying to index on the port names in the Matrix and then look up
the appropriate distance based on the entries in cells A2 and A3.

Any assistance in figuring this out would be greatly appreciated!!

Kind Regards,

 

[Bob Phillips]

Chris,

I got confused where the data is stored (is it in Matrix A2:A31, or on this
sheet D1:AF31), but if it's in D1:AF31, this will get the value you want

=INDEX($D$1:$AF$31,MATCH(A2,$D$1:$D31,0),MATCH(A3,$D$1:$AF$1,0))

I hope you can sort out the correct refernces

 

[Dan E]

Chris,

Just to ensure I understand you and you understand me
Sheet matrix is set up as follows
_A,B,C,D,E
A,0,2,3,4,5
B,2,0,4,5,6
C,3,4,0,6,7
D,4,5,6,0,8
E,5,6,7,8,0
where A -> E represent place names and the numbers are distances
i'm going to assume the the first distance is in cell B2 and is 0 (ie no
distance between A & A (A1 is empty)

On your page like you said A2 has name1 and A3 has name2
So A2 = "A" & A3 = "D" should pull up a distance of 4!

In B2
=INDEX(matrix!A1:F6,MATCH(A2,Matrix!A1:A6,TRUE),MATCH(A3,Sheet3!A1:F1,TRUE))

Hope that's what you need???

Dan E