Return value based on multiple conditions

[jctroxell]

More information
I am trying to have a cell return a value based on multiple conditions. From the table below:

[TABLE="width: 389"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project[/TD]
[TD]Origin[/TD]
[TD]Destination[/TD]
[TD]miles[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alpha[/TD]
[TD]washington[/TD]
[TD]california[/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Alpha[/TD]
[TD]michigan[/TD]
[TD]california[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Alpha[/TD]
[TD]atlanta[/TD]
[TD]ohio[/TD]
[TD="align: right"]750[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Alpha[/TD]
[TD]chicago[/TD]
[TD]ohio[/TD]
[TD="align: right"]356[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Bravo[/TD]
[TD]nashville[/TD]
[TD]pennsylvania[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Bravo[/TD]
[TD]alabama[/TD]
[TD]pennsylvania[/TD]
[TD="align: right"]590[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Bravo[/TD]
[TD]florida[/TD]
[TD]georgia[/TD]
[TD="align: right"]875[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Bravo[/TD]
[TD]texas[/TD]
[TD]georgia[/TD]
[TD="align: right"]250[/TD]
[/TR]
</tbody>[/TABLE]

I want to have a cell (in another sheet) return the mileage by simply inputting the Project name, origin and destination.

So, the formula should first narrow to the matching project in A2 below, then match the origin in B2 below, then match the destination in C2 below and then return the corresponding mileage in D2 below. What formula do I need to put into D2??

[TABLE="width: 364"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]project[/TD]
[TD]origin[/TD]
[TD]destination[/TD]
[TD]miles[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Alpha[/TD]
[TD]Michigan[/TD]
[TD]California[/TD]
[TD]RETURNED MILEAGE[/TD]
[/TR]
</tbody>[/TABLE]


Help!

 

[Jonmo1]

Welcome to the board.

You could do
=SUMPRODUCT(--(A1:A9="Alpha"),--(B1:B9="Washington"),--(C1:C9="california"),D1:D9)

 

[jctroxell]

Not quite what O was looking for, let me try explaining again:

if I have a sheet called projects:

[TABLE="width: 288"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Project Name[/TD]
[TD]Origine[/TD]
[TD]Destination[/TD]
[TD]Miles[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Alpha[/TD]
[TD]Chicago[/TD]
[TD]Newyork[/TD]
[TD="align: right"]510[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Alpha[/TD]
[TD]Chicago[/TD]
[TD]Newyork[/TD]
[TD="align: right"]510[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Alpha[/TD]
[TD]Chicago[/TD]
[TD]Atlanta[/TD]
[TD="align: right"]940[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Alpha[/TD]
[TD]Chicago[/TD]
[TD]Atlanta[/TD]
[TD="align: right"]940[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Alpha[/TD]
[TD]Boston[/TD]
[TD]Newyork[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Alpha[/TD]
[TD]Boston[/TD]
[TD]Newyork[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Alpha[/TD]
[TD]Boston[/TD]
[TD]Atlanta[/TD]
[TD="align: right"]610[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Alpha[/TD]
[TD]Boston[/TD]
[TD]Atlanta[/TD]
[TD="align: right"]610[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Bravo[/TD]
[TD]Detroit[/TD]
[TD]Sand Diego[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Bravo[/TD]
[TD]Detroit[/TD]
[TD]Sand Diego[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Bravo[/TD]
[TD]Boston[/TD]
[TD]Nebraska[/TD]
[TD="align: right"]750[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Bravo[/TD]
[TD]Boston[/TD]
[TD]Nebraska[/TD]
[TD="align: right"]750[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Bravo[/TD]
[TD]Houston[/TD]
[TD]Atlanta[/TD]
[TD="align: right"]350[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]Bravo[/TD]
[TD]Houston[/TD]
[TD]Atlanta[/TD]
[TD="align: right"]350[/TD]
[/TR]
</tbody>[/TABLE]

And then If I have another sheet called analysis:

[TABLE="width: 444"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Project name[/TD]
[TD]Origine[/TD]
[TD]Destination[/TD]
[TD]Miles[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]"enter project name"[/TD]
[TD]"Enter Origine"[/TD]
[TD]"Enter Destinaiton"[/TD]
[TD]"=miles"[/TD]
[/TR]
</tbody>[/TABLE]


I want 'analysis'!D2 to return:

D2= Find all matching 'analysis'!D2 in range 'projects'!A2:A9999999, then of that selection find all matching 'analysis'!B2 in range 'projects'!B2:B99999, then of that selection find all matching 'analysis'!C2 in range 'projects'!C2:C9999, and finally of that selection return the value of 'projects'!D2:D9999

The issue is, as you'll see in the table above, I could have all of the criteria match up multiple times, however I want a product or a sum of all of the miles, I simply want what the mileage is from origin to destination.

Example

Analysis:

[TABLE="width: 444"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Project name[/TD]
[TD]Origine[/TD]
[TD]Destination[/TD]
[TD]Miles[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Alpha[/TD]
[TD]Chcago[/TD]
[TD]Newyork[/TD]
[TD]510
[/TD]
[/TR]
</tbody>[/TABLE]


Rather than:

[TABLE="width: 444"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Project name[/TD]
[TD]Origine[/TD]
[TD]Destination[/TD]
[TD]Miles[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Alpha[/TD]
[TD]Chcago[/TD]
[TD]Newyork[/TD]
[TD]1020 or 260100 [/TD]
[/TR]
</tbody>[/TABLE]


Please help!