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