Mmmmm........

I must have changed something between developing the formula and posting it
because as posted it does not require the " +1 " in the match so use:

=IF(OR(B3="",C3=""),"",IF(ISNA(VLOOKUP(B3,G1:L6,MA TCH(C3,G1:G6,FALSE),FALSE)),"Wrong
data given",VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE),FALS E)))


--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Sandy Mann" wrote in message
...
..... I am still having some issues understanding how I could change
the
"From" with this method and still have it reflect the correct mileage.

Sorry, perhaps it was my fault for not realising what you wanted but your
original post specified only "Admin" as the "From" with
the other name as destinations I assumed that you only wanted to lookup
distances in one direction.

If you construct a table as below:

G H I J K L
1 One Two Three Four Five
2 One 0 10 15 20 25
3 Two 10 0 7 12 19
4 Three 15 7 0 8 10
5 Four 20 12 8 0 9
6 Five 25 19 10 9 0

(But of course use your own correct names and distances in the working
version.)

In B3 enter the "From" - say Three - and in C3 enter the "To" - say Five

now enter the formula:

=VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1,FALSE)

The formula will look down the list in G2:G6 until it finds a match with
B3, then along that row the number of columns returned by the MATCH
function plus 1 (because VLOOKUP is 1 based not zero based), and return
the milage that it find in that cell. The two FALSE argumets are to make
the functions find exact matched only otherwise it could give wrong
results.

That looks fine as long as everyting is in agreement but if B3 or C3 are
empty or contain anything except correct data then the formula will return
#N/A. To get around this wrap if in an IF statement:

=IF(ISNA(VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1, FALSE)),"Wrong Data
Given",VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1,FA LSE))

Of course that will now give "Wrong Data Given" when B3 and/or C3 are
empty which you may not want. You could change "Wrong Data Given" to
smply "" which would return an *enpty" cell but then it would do that for
incorrect destination/starting points as well. You could solve both
probelms with another IF as in:


=IF(OR(B3="",C3=""),"",IF(ISNA(VLOOKUP(B3,G1:L6,MA TCH(C3,G1:G6,FALSE)+1,FALSE)),"Wrong
data given",VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1,FA LSE)))

HTH

Sandy



wrote in
message ...
I am trying to wrap my mind around this-- it makes more sense everytime I
read it. I am still having some issues understanding how I could change
the
"From" with this method and still have it reflect the correct mileage.
Would
I need to make a VLOOKUP table for every possible combination of mileage?