+ Reply to Thread
Results 1 to 10 of 10

Lookup multiple criteria in different tabs to return result

  1. #1
    Registered User
    Join Date
    04-07-2008
    Posts
    27

    Lookup multiple criteria in different tabs to return result

    All,

    I thank you for your help in advance....

    I am trying to lookup two distinct values in two columns (turquoise and green) in 'Cust data' tab and correlate them to the same values in two columns on 'Driver activity' tab, then return a result from column in yellow on 'Driver Activity' tab to populate the driver name in yellow column on 'Cust data' tab.

    File is attached.

    Thanks again!
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Try:

    Please Login or Register  to view this content.
    and follow these steps...

    After copying, adjust the ranges to suit your real data.

    Then confirm the formula with CTRL+SHIFT+ENTER keys not just ENTER you will see { } brackets appear around the formula.

    Then copy the formula down.

    Note: I am not sure the dates in column AE are entered as dates.. to make sure, select column AE and go to Data|Text to Columns and just click Finish.
    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.

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967
    You don't appear to have matching data. Is this a small except from a much larger sheet?
    Martin

  4. #4
    Registered User
    Join Date
    04-07-2008
    Posts
    27
    Quote Originally Posted by mrice
    You don't appear to have matching data. Is this a small except from a much larger sheet?
    Yes, original spreadsheet was 1.2 mb zipped.

  5. #5
    Registered User
    Join Date
    04-07-2008
    Posts
    27
    Sorry, I'm a novice.....I copied the formula to cell B7, adjusted the ranges, confirmed the formula, made sure the dates are in correct format, and am getting #N/A.

    Please advise.

  6. #6
    Registered User
    Join Date
    04-07-2008
    Posts
    27
    These are the ranges I adjusted to:

    Please Login or Register  to view this content.
    In the formula evaluation, the error occurs on the last step:

    =INDEX('Driver Activity'!$B$7:$B$7523,MATCH(1,0,0))

  7. #7
    Registered User
    Join Date
    04-07-2008
    Posts
    27
    The formula appears to work for 5 lines out of 2500, very frustrating.
    I've noticed in the formula evaluation on the #N/As that a true value is not being generated for MATCH(1,('Driver Activity'!$L$7:$L$250='Cust Data'!C7).

    Please advise.

  8. #8
    Registered User
    Join Date
    04-07-2008
    Posts
    27
    Quote Originally Posted by Nadir Soofi
    The formula appears to work for 5 lines out of 2500, very frustrating.
    I've noticed in the formula evaluation on the #N/As that a true value is not being generated for MATCH(1,('Driver Activity'!$L$7:$L$250='Cust Data'!C7).

    Please advise.
    Ok, I'm finally having some sucess with the formula.... about 70% of driver names are showing up.

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Without seeing the entire workbook, it is hard to say what is wrong...

    what have you been doing to increase the number of results you are getting?

    Most likely there is not an exact match somewhere...

    Check for extra spaces in the supposed matches... check your dates and make sure they correspond exactly....

    If you notice I put the INT() function around the date range: 'Driver Activity'!$C$7:$C$7523. This is because those cells also had time in them and the cells in 'Cust Data'!AE did not have time.... that I could see.. but maybe some do. Try wrapping the second part of the condition with an INT() function too.

    In order to fully diagnose the from I would have to see a sample of an error and also see what the supposed match is.

    The file you sent looked incomplete and so I ignored the fact that I got multiple #N/A errors...assuming you cut off the data...

  10. #10
    Registered User
    Join Date
    04-07-2008
    Posts
    27
    Please Login or Register  to view this content.
    After a bit of research, this is what I came up with, and it works flawlessly! Had to define a few names for my ranges.

    Here's a link to the tutorial:

    HTML Code: 

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Multiple lookup and multiple results
    By nicmarty in forum Excel General
    Replies: 1
    Last Post: 04-16-2008, 04:51 PM
  2. COUNTIF with Multiple Criteria
    By digitalzero in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-07-2007, 05:57 PM
  3. Lookup Multiple Results
    By Joffa21 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-18-2006, 01:59 AM
  4. Replies: 1
    Last Post: 10-05-2006, 05:56 PM
  5. Lookup return multiple values?
    By bkube01 in forum Excel General
    Replies: 1
    Last Post: 09-28-2006, 04:44 AM

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