Return Value from cells which match criteria (complex)

[NCoppersmith]

I have a worksheet I want to compute mileage on. Right now we're doing
everything manually, but I want to see if I can automate the process in Excel
without resorting to a lot (if any) VB code.

The formula needs to examine 4 columns for the start point, then 43 or so
columns for the destination point. From there it can reference another sheet
with the mileage between two points and insert the correct value.

The logic looks something like this: "Look for data in array a and in array
b then depending on which columns hold the data, lookup the corresponding
result in worksheet x and enter the result in cell y"

Using the If, match and index functions I can get the result I want, but
only for 1 origination and 1 destination. Trying to extrapolate this out to
4*43 is causing a problem. Do I write a VBA function with all the necessary
formulas? Am I missing a function somewhere? I realize this may not be ideal
for Excel, but unfortunately that's what we are limited to using at this
point.

Right now the data entry person is keying in the load in each shipment, then
looking up her table to see where it started and where it ended and putting
in the mileage between points. So no real calculation is being done in the
worksheet in regards to miles, it's a manual lookup right now based on start
and end points.

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Pete_UK]

In a road atlas you will often see a triangular table like this
showing distances between two cities:

city a b c d e f g
a x
b n x
c n n x
d n n n x
e n n n n x
f n n n n n x
g n n n n n n x

and so on. It is easier if you complete the upper triangle by
reflecting the distances about the diagonal, so that you would have
something like this:

city a b c d e f g
a x n n n n n n
b n x n n n n n
c n n x n n n n
d n n n x n n n
e n n n n x n n
f n n n n n x n
g n n n n n n x


So, if your table of distances is laid out like this on Sheet2,
starting in A1 and going out to column M, then you can have this
formula:

=INDEX(Sheet2!$B$2:$M$14,MATCH(start,Sheet$2!$A$2:$A
$14,0),MATCH(end,Sheet2!$B$1:$M$1))

where start and end are the cells containing the two locations.
Obviously, you need to adjust the ranges to suit your data.

Hope this helps.

Pete