lookup and sumproduct

[Ruth]

Hi

We have a data base that has columns such as start point, load point and
unload point with the distance between each in a separate column. We want to
create a column that will look up the load point and unload point and give us
the distance. The problem is that the load point and unload point are often
times listed up to 5 times, as the start point is different, so the formula
we are using

SUMPRODUCT(table range=cell)*(table range=cell)*(table)

is summing the distances.

What is a formula we can use that will not add the distance together?

 

[Dave Peterson]

So you could just return the first cell found?

Maybe you could use =vlookup() if there's only a single criteria to match.

Or ...

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

 

[daddylonglegs]

Presumably then you also need to use the start point in the formula to get
the correct distance so try

=LOOKUP(2,1/((startpoint_range=startpoint)*(loadpoint=loadpoint_range)*(unloadpoint=unloadpoint_range)),distance_range)

 

[Ruth]

Thank-you very much, we will try it!