if statement i think...

[Motty]

Hi all,

I have two sheets sheet 1, which has three fields: Origin, Dest and
Distance which is between B61605. 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.

 

[Ragdyer]

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.

 

[Motty]

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.

 

[RagDyeR]

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!
=====================================================

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]

Hi,

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

Motty.:cool

 

[RagDyeR]

Thanks for the feed-back.
--

Regards,

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

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.