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

1. ## 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.

2. 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
xdemechanik
---

3. ## paired lookup

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.

4. You already have the paired lookup values in columns B and C of your Mileage Log sheet.

Your formula in D2 should be:

=INDEX(Distances!\$C\$2:\$C\$100,MATCH(1,(Distances!A\$2:A\$100=B2)*(Distances!B\$2:B\$100=C2),0))

and it must then be confirmed with CTRL+SHIFT+ENTER, not just ENTER.. you will see { } brackets appear around the formula... then you can copy it down the column.

5. ## Re:

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?

6. Have a look at Data Validation

Here's a link that shows you how:

http://www.contextures.com/xlDataVal01.html

7. ## validation

thank you once again for your help and promptness!!

8. ## lookup and null values

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?

9. You can enclose your formula within an IF formula that will return a blank or 0 when there is nothing in your destination cell..

e.g. in D2:

``Please Login or Register  to view this content.``
again, confirmed with CTRL+SHIFT+ENTER before copying down.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1