Alternative to Vlookup/Hlookup to return a value.
 

I am trying to prepare a logistics cost analysis and I would like to be able
to do the following:

I have a populated matrix of logistics costs from the origin to destination.
For Example:
From \ To Detroit Chicago Orlando New York Seattle
Detroit $- $20.00 $63.00 $81.00 $95.00
Chicago $45.00 $- $12.00 $35.00 $47.00
Orlando $65.00 $46.00 $- $34.00 $58.00
L.A. $32.00 $54.00 $28.00 $10.00 $26.00
Houston $75.00 $12.00 $41.00 $69.00 $50.00

What I am looking to accomplish is create a spreadsheet that I use to
perform scenario testing. I would like to be able to vary the From/To
locations manually, and have the formula return the transportation cost.

There are several criteria that I need to link into this formula (like
pallet size, pallets/trailer, etc.) - without using vlookup/hlookup functions.

Please help. My email is

For the From and To question: you can combine INDEX() and MATCH() functions
to figure out which row & column intersection you want. MATCH() will tell
you how many rows down you will find your From city & how many columns over
to find the To city. Then use INDEX() to retreive the intersection of the
indicated row and column.

The second part - where you want to link in pallet size and other factors -
are you suggesting you want a way to use a 3, 4, or more dimension lookup?
OUCH!

Give us some more detail on how those other factors enter into this.

"Looking for Excel Wizzards" wrote:

I am trying to prepare a logistics cost analysis and I would like to be able
to do the following:

I have a populated matrix of logistics costs from the origin to destination.
For Example:
From \ To Detroit Chicago Orlando New York Seattle
Detroit $- $20.00 $63.00 $81.00 $95.00
Chicago $45.00 $- $12.00 $35.00 $47.00
Orlando $65.00 $46.00 $- $34.00 $58.00
L.A. $32.00 $54.00 $28.00 $10.00 $26.00
Houston $75.00 $12.00 $41.00 $69.00 $50.00

What I am looking to accomplish is create a spreadsheet that I use to
perform scenario testing. I would like to be able to vary the From/To
locations manually, and have the formula return the transportation cost.

There are several criteria that I need to link into this formula (like
pallet size, pallets/trailer, etc.) - without using vlookup/hlookup functions.

Please help. My email is