+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    08-05-2008
    Location
    GA
    Posts
    9

    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. #2
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520
    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
    ---

  3. #3
    Registered User
    Join Date
    08-05-2008
    Location
    GA
    Posts
    9

    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.
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    08-05-2008
    Location
    GA
    Posts
    9

    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. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Have a look at Data Validation

    Here's a link that shows you how:

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

  7. #7
    Registered User
    Join Date
    08-05-2008
    Location
    GA
    Posts
    9

    validation

    thank you once again for your help and promptness!!

  8. #8
    Registered User
    Join Date
    08-05-2008
    Location
    GA
    Posts
    9

    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. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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