Compare values of 2 columns in one worksheet to 2 columns in another worksheet

I need to compare the text in 2 columns of one worksheet to that of 2 columns in another worksheet to get a value.

for example.

worksheet one has 3 columns, Start, End and Miles. I need to compare the start and end in worksheet 1 to the start and end in worksheet 2. when a match is found in both start and end in worksheet 2, get the corresponding mileage found in the Miles column and transfer it to miles in worksheet 1.

Assume data in cols A to C* in Sheet2, from row2 down
*Start, End and Miles

In Sheet1,
With paired lookup values in A2:B2 down (ie Start, End)
Place in C2, then array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX(Sheet2!C$2:C$100,MATCH(1,(Sheet2!A$2:A$100=A2)*(Sheet2!B$2:B$100=B2),0))
Copy C2 down as far as required. Adapt the range to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik
---

Thanks for the fast response!!!
one question for ya, how would i go about making a paired lookup? I attached my worksheet incase it would help.

Thanks its working now, i had just hit enter instead of the ctrl,shift, enter.
thanks alot.
also, would there be a way in excel, to allow me to use a "pull down menu" of the possible starting and ending locations, that are in my distances worksheet, but if its a new location allow me to just type it in?
pretty much to just bypass having to type the more frequently visited locations.

or would i have to be usind access to create something like this?

thank you once again for your help and promptness!!

i have a pair look up function in my worksheet, for the start and ending locations, it works fine, i also made the option of having up to 2 more locations per trip and that works fine, my problem comes when i have only one destination. how could i make destination2 and 3 accept a null value, or no input and leave the miles as a 0?