lookup value for form multiple combination in a Matrix

[Pamanabh]

hi , i m trying to retrieve the value from a TAT matrix for differen
combinations ,
here attaching the file , working in the result sheet, where it wil
lookup for TAT for the different Origin to their respective Destination
as per Sheet TAT. Plz assist with the possible solution

+-------------------------------------------------------------------
|Filename: TAT REPORT NEW.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=341
+-------------------------------------------------------------------

 

[Pamanabh]

Dear Pamanabh, Good Afternoon.

Could you show us a little example about your desired results to easie
our help to you?

Remember that you know a lot about your data and how they work together
and for us it´s the first time that we see them, isn´t?

Hi !!1 good Evening .

Let me Give a breif...exmple

Sheet 1

Org Dest TAT
BBI CCU
CTK BBI
BKS BBI
BHG CTK

Col A contain : Origin City code
Col B contain : Destination Code

Col C TAT to be calculate(?)


Sheet 2 MATRIX (134 cols and 134 rows)

Stn CCU BBI BHG BKS BLS CTK
CCU 1 1 1 1 1 1
BBI 1 1 2 2 2 2
BHG 1 2 1 2 2 2
BKS 1 2 2 1 2 2
BLS 1 2 2 2 1 2
CTK 1 2 2 2 2 1
GAU 1 2 2 2 2 2
IMF 1 2 2 2 2 2

Sheet 2 is matrix contain the time in Days if any shipment trave
between any two city. we can assume origin or destination to any of th
axis as its a Square matrix containing same codes in x and y axis.

Now have to check for TAT in Sheet 1. (COL C )
i m trying to pu Vlookup and Match but is not working ...

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Mazzaropi]

Hi !!1 good Evening .
Let me Give a breif...exmple
Sheet 1
Org Dest TAT
BBI CCU
CTK BBI
BKS BBI
BHG CTK
Col A contain : Origin City code
Col B contain : Destination Code
Col C TAT to be calculate(?)

Sheet 2 MATRIX (134 cols and 134 rows)
Stn CCU BBI BHG BKS BLS CTK
CCU 1 1 1 1 1 1
BBI 1 1 2 2 2 2
BHG 1 2 1 2 2 2
BKS 1 2 2 1 2 2
BLS 1 2 2 2 1 2
CTK 1 2 2 2 2 1
GAU 1 2 2 2 2 2
IMF 1 2 2 2 2 2

Sheet 2 is matrix contain the time in Days if any shipment trave
between any two city. we can assume origin or destination to any of th
axis as its a Square matrix containing same codes in x and y axis.
Now have to check for TAT in Sheet 1. (COL C )
i m trying to pu Vlookup and Match but is not working ....

Dear *Pamanabh*, Good Evening.

Now your example was VERY CLEAR.

As you said before
"-i m trying to pu Vlookup and Match but is not working ....-
You´re right.

The simple formula uses only VLOOKUP and MATCH.

Take a look at it and tell me if it worked for you.

Have a nice day

+-------------------------------------------------------------------
|Filename: 26-04-2012-ExcelBanter_Searching_TAT_HELP_from_BRAZIL.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=348
+-------------------------------------------------------------------

 

[Pamanabh]

Dear *Pamanabh*, Good Evening.

Now your example was VERY CLEAR.

As you said before
"-i m trying to pu Vlookup and Match but is not working ....-
You´re right.

The simple formula uses only VLOOKUP and MATCH.

Take a look at it and tell me if it worked for you.

Have a nice day.

Hi Good evening !!!!!!

Yes its working !!!! thanks !!!!

got entire formula just want know the logic behind passing Argumen
+1,

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Mazzaropi]

Hi Good evening !!!!!!
Yes its working !!!! thanks !!!!
got entire formula just want know the logic behind passing Argumen
+1,o

Dear *Pamanabh*, Good Afternoon.

The reason about *+1* is simple.

In this case the Function MATCH starts searching at column 2.

The Function VLOOKUP always starts numbering columns as column 1.
As the used formula is indexed, you need to add +1 to capture the righ
answer.

Try to remove the +1 and observe the answer.
It will be the same value of a previous column.

Is it OK?

Have a nice day

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Pamanabh]

Dear *Pamanabh*, Good Afternoon.

The reason about *+1* is simple.

In this case the Function MATCH starts searching at column 2.

The Function VLOOKUP always starts numbering columns as column 1.
As the used formula is indexed, you need to add +1 to capture the righ
answer.

Try to remove the +1 and observe the answer.
It will be the same value of a previous column.

Is it OK?

Have a nice day.

yes its working as required!!!!! Thanks a lot buddy !!!!!! :

+-------------------------------------------------------------------
+-------------------------------------------------------------------