Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup and sumproduct
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? -- Thank-you! Ruth |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup and sumproduct
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)) Ruth wrote: 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? -- Thank-you! Ruth -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup and sumproduct
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=loadpoin t_range)*(unloadpoint=unloadpoint_range)),distance _range) "Ruth" wrote: 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? -- Thank-you! Ruth |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup and sumproduct
Thank-you very much, we will try it!
-- Thank-you! Ruth "daddylonglegs" wrote: 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=loadpoin t_range)*(unloadpoint=unloadpoint_range)),distance _range) "Ruth" wrote: 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? -- Thank-you! Ruth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Lookup as condition in sumproduct formula | Excel Worksheet Functions | |||
maybe lookup/index/match/sumproduct | Excel Discussion (Misc queries) | |||
Sumproduct and horizontal lookup | Excel Discussion (Misc queries) | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
from sumproduct to a lookup please | Excel Worksheet Functions |