Basic Lookup

[James Tebb]

My table has three columns - JOURNEY START / JOURNEY END / DISTANCE.
On a seperate sheet I enter my start point in one cell, my end point in the next. I want it to lookup the values and tell me the distance in the next cell.
I understand basic lookups but how do get it evaluate JOURNEY START and JOURNEY END?

 

[texasalynn]

try this

Excel Workbook
	A	B	C
1	JOURNEY START	JOURNEY END	DISTANCE.
2	12441	12775	334
3
4
Sheet3

Excel 2003

Cell Formulas
Range	Formula
C2	=+B2-A2

Excel Workbook
	A	B	C
8	JOURNEY START	JOURNEY END	Distance
9	12441	12775	334
Sheet2

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[James Tebb]

Thanks. BUT!!
I should have explained. The Journey Stsrt and End are place names. With the current formula i get a VALUE error message

 

[texasalynn]

post a small sample of your data and with the expected results

 

[stefankemp]

Try this:

=SUMPRODUCT((Sheet1!A2:A10=A2)*(Sheet1!B2:B10=B2)*(Sheet1!C2:C10))

Amend ranges as appropriate.