+ Reply to Thread
Results 1 to 6 of 6

What formulas can I use to find matches based on multiple criteria

  1. #1
    Registered User
    Join Date
    01-30-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    35

    What formulas can I use to find matches based on multiple criteria

    Hello, I’m seeking the sacred knowledge of you excel experts. I’m seeking to broaden my excel knowledge and learn how to use a formula more efficiently in Excel 2010.

    The attached spreadsheet shows a list of contracts our customers are on.

    Matches Based on more than one criteria.xlsx
    These lists can easily be over 30,000 lines at times. I want to quickly be able to sort or filter this list according to matches with multiple criteria. In the example Column G Shows my customer criteria and column H shows my contract criteria. In column F I’ve created a formula that will return “true” only if the customer data for that row matches the customer and contract criteria I set up in columns G and H.

    The Formula I used is =AND(ISNUMBER(MATCH(A2,G$2:G$10,0)),ISNUMBER(MATCH(E2,H$2:H$7,0)))

    So with this formula I will get the result True for every row that has Customer 1 through 9 and is also on contract 1 through 6

    What are some other, possibly better formulas I could use to get this information? This example just uses two criteria (customer and contract) but sometimes I have 3 or 4 criteria and my formula wont work in these cases (too many arguments right?). Is there a formula that would be better suited for more criteria?
    Thank you

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: What formulas can I use to find matches based on multiple criteria

    Nothing wrong with that formula.
    It would definately work with 3 or 4 criteria as well.
    I think you can have up to 64 expressions in a single AND function.

    Perhaps this might be easier

    =AND(COUNTIF(G$2:G$10,A2),COUNTIF(H$2:H$7,E2))

  3. #3
    Registered User
    Join Date
    01-30-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: What formulas can I use to find matches based on multiple criteria

    yeah that works as well. I didn't realize that you could use formulas that return numbered results with the AND formula. I thought you had to use True false formulas, hence my use of ISNUMBER. You gave me the idea to remove ISNUMBER and this works as well. Thanks

    To get a result like this do you have to start with AND?

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: What formulas can I use to find matches based on multiple criteria

    You can use Number results in place of True/False
    0 = FALSE
    ANY OTHER NUMBER = TRUE


    You don't HAVE to use AND, but it's easier..
    You could do
    =(COUNTIF(...)+COUNTIF(...))=2

  5. #5
    Registered User
    Join Date
    01-30-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: What formulas can I use to find matches based on multiple criteria

    I see, thanks for your help. This is exactly what I was hoping to gain

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: What formulas can I use to find matches based on multiple criteria

    You're welcome.

+ 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. Replies: 2
    Last Post: 03-03-2014, 10:03 PM
  2. Replies: 6
    Last Post: 03-06-2013, 04:02 AM
  3. Find unique matches for multiple criteria within a row of cells
    By Chase in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2012, 03:11 PM
  4. Replies: 8
    Last Post: 02-15-2012, 05:05 AM
  5. Replies: 2
    Last Post: 07-19-2011, 04:08 PM

Tags for this Thread

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