+ Reply to Thread
Results 1 to 2 of 2

Find matching lines based on multiple criteria

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2010

    Find matching lines based on multiple criteria

    I use a lot of match, index, and countif formulas in a variety of different ways, however I have a hard time knowing how to use these when I need to find "matches" based multiple criteria.

    The attached spreadsheet shows an example of what I'm looking for. The sheet is of a list of two customers and the contracts they are on. I want to create a formula in column J for each row, that returns a result only if the customer number and contract number match, but the end dates do not for any other row of data in the list. The two blue lines in the attachment show such an instance. So I would want my formula in column J to return a result (either number or word, just something to sort by) in cells J6 and J7

    The thing i have a hard time doing in my formula attempts is telling excel to compare the data in a given row against only against data in other rows as opposed to data in columns. E.G. I can return results where the customer number matches another row with the same customer and when the contract number matches, but not where the customer number and contract number are matching on the same row.

    I hope I have explained this clearly. In the past I have done this task observationally but sometimes I deal with thousands of lines and anything that could speed this up would be greatly appreciated.
    Attached Files Attached Files
    Last edited by Jonathan68; 02-13-2014 at 05:43 PM.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016

    Re: Find matching lines based on multiple criteria

    I'm not sure what you are asking for your dataset. Do you want J6:J7 flagged because they have the same contract, customer name, and different end dates? What makes those rows special?

    As far as multi-criteria indices go, you can use: INDEX(table,MATCH(criteria2,IF(criteria range1=criteria1, criteria range2),0))

    Example: INDEX(A1:A5,MATCH(D10,IF(C1:C5=E10,B1:B5),0)

    If C1:C5 = E10, then Match D10 against the rows of B1:B5

    Another popular variation is INDEX(table,MATCH(criteria1&criteria2,criteria range1&criteria range2,0))

    Example: INDEX(A1:A5,MATCH(D10&E10,B1:B5&C1:C5,0))

    These both have to be entered as an array using Ctrl+Shift+Enter.
    Last edited by daffodil11; 02-13-2014 at 06:08 PM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

+ 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. [SOLVED] Find last date matching multiple criteria
    By SimonLock in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-09-2014, 02:10 PM
  2. Replies: 12
    Last Post: 06-29-2012, 10:13 AM
  3. Find Matching Value based on multiple criteria
    By Sisonkemdala in forum Excel General
    Replies: 2
    Last Post: 05-09-2011, 07:26 AM
  4. Replies: 1
    Last Post: 12-14-2009, 02:24 PM
  5. Create multiple lines based upon criteria
    By SystemsAccountant in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-05-2008, 01:14 PM

Tags for this Thread


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