I have a data base that I need to enter the distance between 2 locations.

ei


From To NT
KM
Windsor Detroit 15476.5936
2
Windsor Detroit 15709.2
2
Windsor Detroit 15886.6864
2
Meldrum Bay Muskegon 5000 541
etc...

I have a table that gives the distance between two places.

ei

From To KM
Badgeley Island Midland 206
Burns Harbour Port Colborne 1340
Burns Harbour Sarnia 919
Burns Harbour Sault Ste Marie 690
Bruce Mines Bay City 390
etc...

I tried the following formula to look up the distance

=VLOOKUP(E6:F6,'Distance Key'!A2:C163,3)

But it didn't work.

How is this done?

--
:)

This gets a little tricky since you're looking up 2 values. SumProduct tends
to be good in this case. Suppose your distance table is in cells A3:C8.
From is in Column A, To is in Column B and the Distance in Column C.

Suppose the formula you want to calculate has its From in A12, its To in B12
and you want the answer to appear in C12. The following formula in C12
should do the trick:

=SUMPRODUCT((A12=$A$3:$A$8)*(B12=$B$3:$B$8)*($C$3: $C$8))

"Ruth" wrote:

I have a data base that I need to enter the distance between 2 locations.

ei


From To NT
KM
Windsor Detroit 15476.5936
2
Windsor Detroit 15709.2
2
Windsor Detroit 15886.6864
2
Meldrum Bay Muskegon 5000 541
etc...

I have a table that gives the distance between two places.

ei

From To KM
Badgeley Island Midland 206
Burns Harbour Port Colborne 1340
Burns Harbour Sarnia 919
Burns Harbour Sault Ste Marie 690
Bruce Mines Bay City 390
etc...

I tried the following formula to look up the distance

=VLOOKUP(E6:F6,'Distance Key'!A2:C163,3)

But it didn't work.

How is this done?

--
:)

Hi Art

I did this and it worked for the first row, but for some reason, when I
copied it down it didn't. This is what the formulas look like:

in row 6
=SUMPRODUCT((E6=Distance!A2:$A$163)*(F6=Distance!$ B$2:$B$163)*(Distance!$C$2:$C$163))

in row 7
=SUMPRODUCT((E7=Distance!A3:$A$163)*(F7=Distance!$ B$2:$B$163)*(Distance!$C$2:$C$163))

in row 8
=SUMPRODUCT((E8=Distance!A4:$A$163)*(F8=Distance!$ B$2:$B$163)*(Distance!$C$2:$C$163))

which seems right to me, the data that it is looking against is always in
the same cells, but only row 6 worked-- how can I fix it?

--
:)


"Art" wrote:

This gets a little tricky since you're looking up 2 values. SumProduct tends
to be good in this case. Suppose your distance table is in cells A3:C8.
From is in Column A, To is in Column B and the Distance in Column C.

Suppose the formula you want to calculate has its From in A12, its To in B12
and you want the answer to appear in C12. The following formula in C12
should do the trick:

=SUMPRODUCT((A12=$A$3:$A$8)*(B12=$B$3:$B$8)*($C$3: $C$8))

"Ruth" wrote:

I have a data base that I need to enter the distance between 2 locations.

ei


From To NT
KM
Windsor Detroit 15476.5936
2
Windsor Detroit 15709.2
2
Windsor Detroit 15886.6864
2
Meldrum Bay Muskegon 5000 541
etc...

I have a table that gives the distance between two places.

ei

From To KM
Badgeley Island Midland 206
Burns Harbour Port Colborne 1340
Burns Harbour Sarnia 919
Burns Harbour Sault Ste Marie 690
Bruce Mines Bay City 390
etc...

I tried the following formula to look up the distance

=VLOOKUP(E6:F6,'Distance Key'!A2:C163,3)

But it didn't work.

How is this done?

--
:)

Never mind-- it worked! I just redid it, must have had some little quirk in it

Thank-you very much!!!!
--
:)


"Art" wrote:

This gets a little tricky since you're looking up 2 values. SumProduct tends
to be good in this case. Suppose your distance table is in cells A3:C8.
From is in Column A, To is in Column B and the Distance in Column C.

Suppose the formula you want to calculate has its From in A12, its To in B12
and you want the answer to appear in C12. The following formula in C12
should do the trick:

=SUMPRODUCT((A12=$A$3:$A$8)*(B12=$B$3:$B$8)*($C$3: $C$8))

"Ruth" wrote:

I have a data base that I need to enter the distance between 2 locations.

ei


From To NT
KM
Windsor Detroit 15476.5936
2
Windsor Detroit 15709.2
2
Windsor Detroit 15886.6864
2
Meldrum Bay Muskegon 5000 541
etc...

I have a table that gives the distance between two places.

ei

From To KM
Badgeley Island Midland 206
Burns Harbour Port Colborne 1340
Burns Harbour Sarnia 919
Burns Harbour Sault Ste Marie 690
Bruce Mines Bay City 390
etc...

I tried the following formula to look up the distance

=VLOOKUP(E6:F6,'Distance Key'!A2:C163,3)

But it didn't work.

How is this done?

--
:)