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:

	A	B	C	D
1	Project	Origin	Destination	miles
2	Alpha	washington	california	1500
3	Alpha	michigan	california	1000
4	Alpha	atlanta	ohio	750
5	Alpha	chicago	ohio	356
6	Bravo	nashville	pennsylvania	2000
7	Bravo	alabama	pennsylvania	590
8	Bravo	florida	georgia	875
9	Bravo	texas	georgia	250

<tbody>
</tbody>

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??

	A	B	C	D
1	project	origin	destination	miles
2	Alpha	Michigan	California	RETURNED MILEAGE

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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:

	A	B	C	D
1	Project Name	Origine	Destination	Miles
2	Alpha	Chicago	Newyork	510
3	Alpha	Chicago	Newyork	510
4	Alpha	Chicago	Atlanta	940
5	Alpha	Chicago	Atlanta	940
6	Alpha	Boston	Newyork	120
7	Alpha	Boston	Newyork	120
8	Alpha	Boston	Atlanta	610
9	Alpha	Boston	Atlanta	610
10	Bravo	Detroit	Sand Diego	250
11	Bravo	Detroit	Sand Diego	250
12	Bravo	Boston	Nebraska	750
13	Bravo	Boston	Nebraska	750
14	Bravo	Houston	Atlanta	350
15	Bravo	Houston	Atlanta	350

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

And then If I have another sheet called analysis:

	A	B	C	D
1	Project name	Origine	Destination	Miles
2	"enter project name"	"Enter Origine"	"Enter Destinaiton"	"=miles"

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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:

	A	B	C	D
1	Project name	Origine	Destination	Miles
2	Alpha	Chcago	Newyork	510

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Rather than:

	A	B	C	D
1	Project name	Origine	Destination	Miles
2	Alpha	Chcago	Newyork	1020 or 260100

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Please help!