I had a feeling that would be the case. Oh well!

--
Biff
Microsoft Excel MVP


"Paul W" wrote in message
...
I do not think there is a way to accomplish what I am looking for. This
formula below returned a circular reference error. Even the idea behind it
though would work only if I were building the spreadsheet from scratch and
not just populating the "miles" field.
Thank you for your help.

"T. Valko" wrote:

Assume this data is in the range A2:E4 -

Comma delimited columns

store,address,2nd store,address,Miles
#1,1 1st st or,#2,23 2nd st CA,485
#1,1 1st st or,#3,48 a st ca,385
#1,1 1st st or,#4,65 r st ca,426

To lookup: #2, 23 2nd st ca, #1,1 1st st or

A5 = #2
B5 = 23 2nd st ca
C5 = #1
D5 = 1 1st st or

=SUMPRODUCT(--(A2:A4=C5),--(B2:B4=D5),--(C2:C4=A5),--(D2:D4=B5),E2:E4)


--
Biff
Microsoft Excel MVP


"Paul W" wrote in message
...
Yes, I think you get the idea. The sheets are 200 to 1300 rows long so
the
result to the 2nd one would be below somewhere. The idea would be to
paste
the formula into the whole sheet and start filling in the miles and as
you
go
each "second set" would auto populate and you would finish in half the
time.

"T. Valko" wrote:

OK, that helps...but it's not entirely clear!

So, based on that sample my best guess for the first result is 485 but
I
don't see a result for the second one.

--
Biff
Microsoft Excel MVP


"Paul W" wrote in message
...
I will try, The spreadsheet is at work and i am home now.
store address 2nd store address
Miles
#1 1 1st st or #2 23 2nd st CA
485
#1 1 1st st or #3 48 a st ca
385
#1 1 1st st or #4 65 r st ca
426
#2 23 2nd st ca #1 1 1st st or
This
is
the info I need
#2 23 2nd st ca #3 48 a st ca
This
is
the info I need

"T. Valko" wrote:

Can you post a small sample of the data that demonstrates what
you're
looking for? From your written description I'm have a hard time
visualizing
the setup and what you're trying to do.

--
Biff
Microsoft Excel MVP


"Paul W" wrote in message
...
Pete, Thanks for the idea. It is a good one. The problem is, I
have
to
have a
programmer import the file into an AS400 program once I have
figured
the
time
and miles and the 2d won't work for that.
Does anyone using maybe VBA have any ideas?

"smartin" wrote:

Pete_UK wrote:
Have you thought about doing the mileage chart as a 2-D table?
Four
stores A to D would be laid out like this:

A B C D
A -
B -
C -
D -

where the vertical list is the "from" store and the horizontal
list
is
the "to" store. Obviously, the diagonal is meaningless (or all
zero).
The top triangle is a mirror image of the bottom triangle,
although
you could just have a single triangle, as it doesn't really
matter
which is "from" and which is "to". You often get such mileage
charts
in road atlases.

Hope this helps.

Pete

On Aug 29, 11:49 pm, Paul W <Paul

wrote:
I have a spreadsheet that I am populating with miles from
point
to
point.
Each spreadsheet has the same point tp oint listed twice, the
second
time in
reverse order. I would love to have a formula that looks
throught
the
entire
K Column for the point in the D column and the D column for
the
point
in the
K column and return the value from the same row in the P
column.
Basically once I have looked up the miles from a store to
another
store I
don't want to have to find the row below that has the same
two
store
in
reverse order and populate the miles field with the same
information
Is this possible?


Pete,

Good idea!