Hi all,

I have two sheets sheet 1, which has three fields: Origin, Dest and
Distance which is between B6:D1605. The second sheet has the same
fields Origin, Dest and distance but this time the parameter is A3:C782
and at the moment the distance (c column is blank i want to fill it).
What i want to do is have a statement that checks if the Origin and
Dest in sheet 2 is equal to the first (this is because there are
duplicates in sheet 1 of combination of origin and dest) origin and
dest of sheet 1 then display the distance of sheet1 to its
corresponding field in sheet 2.

e.g.

sheet 1
origin
1
Dest
2
Distance
200

Sheet 2
origin
1
Dest
2
Distance


Need to get distances for sheet 2.

I think this requires an if statement but I am not sure. . I
have tried the various Lookups and index but could not get them to
work.

Thanks in advance for any help,

Motty.


--
Motty
------------------------------------------------------------------------
Motty's Profile: http://www.excelforum.com/member.php...o&userid=29233
View this thread: http://www.excelforum.com/showthread...hreadid=490294

Try this *array* formula in C4 of Sheet2:

=INDEX(Sheet1!$D$6:$D$1605,MATCH(1,(Sheet1!$B$6:$B $1605=A4)*(Sheet1!$C$6:$C$1605=B4),0))

And drag down to copy as needed.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Motty" wrote in
message ...

Hi all,

I have two sheets sheet 1, which has three fields: Origin, Dest and
Distance which is between B6:D1605. The second sheet has the same
fields Origin, Dest and distance but this time the parameter is A3:C782
and at the moment the distance (c column is blank i want to fill it).
What i want to do is have a statement that checks if the Origin and
Dest in sheet 2 is equal to the first (this is because there are
duplicates in sheet 1 of combination of origin and dest) origin and
dest of sheet 1 then display the distance of sheet1 to its
corresponding field in sheet 2.

e.g.

sheet 1
origin
1
Dest
2
Distance
200

Sheet 2
origin
1
Dest
2
Distance


Need to get distances for sheet 2.

I think this requires an if statement but I am not sure. . I
have tried the various Lookups and index but could not get them to
work.

Thanks in advance for any help,

Motty.


--
Motty
------------------------------------------------------------------------
Motty's Profile:
http://www.excelforum.com/member.php...o&userid=29233
View this thread: http://www.excelforum.com/showthread...hreadid=490294

Hi RD,

Thanks for the reply, i pasted the formula but get an error: #value!. I
went to the calculation steps an it underlines the following:
*=INDEX(Sheet1!$D$6:$D$1605,MATCH(1,(_Sheet1!$B$6: $B_$1605_=A4)*(_Sheet1!$C$6:$C$1605_=B4),0))*
in the statement as the error part in the formula

Any ideas on why this may be the case, Thanks in advance for any help,

Motty.


--
Motty
------------------------------------------------------------------------
Motty's Profile: http://www.excelforum.com/member.php...o&userid=29233
View this thread: http://www.excelforum.com/showthread...hreadid=490294

Did you remember to enter the formula as an *array* formula as I stated?
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

This means you click in the formula bar,
And then hold down <Ctrl AND <Shift,
Then hit <Enter.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Motty" wrote in
message ...

Hi RD,

Thanks for the reply, i pasted the formula but get an error: #value!. I
went to the calculation steps an it underlines the following:
*=INDEX(Sheet1!$D$6:$D$1605,MATCH(1,(_Sheet1!$B$6: $B_$1605_=A4)*(_Sheet1!$C$
6:$C$1605_=B4),0))*
in the statement as the error part in the formula

Any ideas on why this may be the case, Thanks in advance for any help,

Motty.


--
Motty
------------------------------------------------------------------------
Motty's Profile:
http://www.excelforum.com/member.php...o&userid=29233
View this thread: http://www.excelforum.com/showthread...hreadid=490294

Hi,

RD I do apologise I did not enter as an array like you said, it finally
does work, thank you very much.

Motty.


--
Motty
------------------------------------------------------------------------
Motty's Profile: http://www.excelforum.com/member.php...o&userid=29233
View this thread: http://www.excelforum.com/showthread...hreadid=490294

Thanks for the feed-back.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Motty" wrote in
message ...

Hi,

RD I do apologise I did not enter as an array like you said, it finally
does work, thank you very much.

Motty.


--
Motty
------------------------------------------------------------------------
Motty's Profile:
http://www.excelforum.com/member.php...o&userid=29233
View this thread: http://www.excelforum.com/showthread...hreadid=490294