Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Index and Match functions to lookup data in another worksheet
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Index and Match functions to lookup data in another worksheet
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 -- HTH Bob Phillips "Chris" wrote in message om... 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,T RUE,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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Index and Match functions to lookup data in another worksheet
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),MA TCH(A3,Sheet3!A1:F1,TRUE)) Hope that's what you need??? Dan E "Chris" wrote in message om... 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,T RUE,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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to stop index match returning data where lookup cell empty | Excel Worksheet Functions | |||
Using Index & Match functions to find data on separate worksheet. | Excel Worksheet Functions | |||
Only text values matching using index/match lookup - data type pro | Excel Worksheet Functions | |||
Match and index functions: corrlating data from 2 worksheets | Excel Worksheet Functions | |||
The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) |