+ Reply to Thread
Results 1 to 16 of 16

Lookup to match multiple criteria and return multiple results excluding duplicates.

  1. #1
    Registered User
    Join Date
    01-12-2018
    Location
    Grantham, England
    MS-Off Ver
    13
    Posts
    10

    Lookup to match multiple criteria and return multiple results excluding duplicates.

    Hi Everyone.
    I'm trying to create a sheet to pull answers to a specific data lookup into a new sheet.
    I want to import the data then the sheet automatically lookup 2 criteria and pull the unique records from the data that match the criteria.
    The criteria needs to be either text or number format or a mixture of both.
    I've attached a file which contains similar data to what i will be using.
    Thanks in advance
    Kris
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Try this in H2:

    =IFERROR(INDEX(C$2:C$97,MATCH(0,IF(F$1&F$2=A$2:A$97&B$2:B$97,COUNTIF(H$1:H1,C$2:C$97),""),0)),"") Ctrl Shift Enter

  3. #3
    Registered User
    Join Date
    01-12-2018
    Location
    Grantham, England
    MS-Off Ver
    13
    Posts
    10

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Hi thanks for the quick response that formula just returns a 0 any ideas why?

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Try

    in I1

    =IFERROR(INDEX(Sheet1!C:C,SMALL(IF((Sheet1!$A$2:$A$97=$F$1)*(Sheet1!$B$2:$B$97=$F$2),ROW(Sheet1!$A$2:$A$97)),(ROWS($1:1)-1)*3+1)),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Delete what is already in column H.

    Now put the formula from post #2 into cell H2 and confirm it with Ctrl Shift Enter instead of just enter. Then drag the formula down column H and you will get the expected results.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Formula in post #2 (as instructed!)


    ..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  7. #7
    Registered User
    Join Date
    01-12-2018
    Location
    Grantham, England
    MS-Off Ver
    13
    Posts
    10

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Quote Originally Posted by JohnTopley View Post
    Try

    in I1

    =IFERROR(INDEX(Sheet1!C:C,SMALL(IF((Sheet1!$A$2:$A$97=$F$1)*(Sheet1!$B$2:$B$97=$F$2),ROW(Sheet1!$A$2:$A$97)),(ROWS($1:1)-1)*3+1)),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Brilliant thank you that is perfect.

    Quote Originally Posted by 63falcondude View Post
    Delete what is already in column H.

    Now put the formula from post #2 into cell H2 and confirm it with Ctrl Shift Enter instead of just enter. Then drag the formula down column H and you will get the expected results.
    I did do that and it just came up with a 0
    Johns formula works perfectly though so thanks alot for all the help.

  8. #8
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Hi, Kris!

    You can use this formula:
    [B1] : Criteria A
    [B2] : Criteria B
    [D2] : =IFERROR(INDEX(Sheet1!$C$2:$C$97,MATCH(,INDEX(COUNTIF(D$1:D1,Sheet1!C$2:C$97)/(Sheet1!A$2:A$97=B$1)/(Sheet1!B$2:B$97=B$2),),)),"")
    D2 formula drag it down.

    Blessings!
    Attached Files Attached Files

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    I'm not sure why you were unable to get the formula from post #2 working but I'm glad that you now have a working solution.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Formula from 63Falcondude is better in that it is "dynamic" wheres mine assumes blocks of 3. And it does work if entered correctly as per post #5.

  11. #11
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2010
    Posts
    587

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    I propose such a formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-12-2018
    Location
    Grantham, England
    MS-Off Ver
    13
    Posts
    10

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Further to pulling the data is it then possible to sort the data into an alphabetical order.
    (i don't know if i should start a separate thread for this or not).
    Thanks Kris

  13. #13
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Hi again, Kris!

    Quote Originally Posted by kris3012 View Post
    pulling the data is it then possible to sort the data into an alphabetical order.
    Change the formula with this one (non-cse):
    [D2] : =IFERROR(INDEX(Sheet1!C$2:C$97,MATCH(AGGREGATE(15,6,COUNTIF(Sheet1!C$2:C$97,"<"&Sheet1!C$2:C$97)/(COUNTIF(D$1:D1,Sheet1!C$2:C$97)=0)/(Sheet1!A$2:A$97=B$1)/(Sheet1!B$2:B$97=B$2),1),INDEX(COUNTIF(Sheet1!C$2:C$97,"<"&Sheet1!C$2:C$97),),)),"")

    And drag it down. Blessings!
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    01-12-2018
    Location
    Grantham, England
    MS-Off Ver
    13
    Posts
    10

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Quote Originally Posted by johnmpl View Post
    Hi again, Kris!



    Change the formula with this one (non-cse):
    [D2] : =IFERROR(INDEX(Sheet1!C$2:C$97,MATCH(AGGREGATE(15,6,COUNTIF(Sheet1!C$2:C$97,"<"&Sheet1!C$2:C$97)/(COUNTIF(D$1:D1,Sheet1!C$2:C$97)=0)/(Sheet1!A$2:A$97=B$1)/(Sheet1!B$2:B$97=B$2),1),INDEX(COUNTIF(Sheet1!C$2:C$97,"<"&Sheet1!C$2:C$97),),)),"")

    And drag it down. Blessings!
    Hi John
    Thanks for the reply i see it working in the file but when i try to change it for my own data set it doesnt work.
    i tried to copy and paste the formula it shows the first answer but when i drag it down it shows the same answer, not really sure what im doing wrong.
    the data set i need the formula for contains around 35,000 lines(i dont know if this affects your code).
    Cheers Kris

  15. #15
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Quote Originally Posted by kris3012 View Post
    when i try to change it for my own data set it doesnt work
    Try to upload a extract of your data (into an Excel), and show me your try. I'll try to check it an seeing what happened. Blessings!

  16. #16
    Registered User
    Join Date
    01-12-2018
    Location
    Grantham, England
    MS-Off Ver
    13
    Posts
    10

    Re: Lookup to match multiple criteria and return multiple results excluding duplicates.

    Hi John
    Thanks I think I've got it to work now (not sure what i did lol)
    Thanks for your help

+ 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. Lookup/Match with multiple results, removing duplicates
    By c1nn4m0n in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2017, 07:30 AM
  2. Count from a text list, excluding duplicates, with multiple criteria
    By Ecervantes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-18-2017, 10:42 PM
  3. Index and match formula, attempting to return results with multiple criteria.
    By mahalek1976 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-02-2017, 12:43 AM
  4. Replies: 3
    Last Post: 06-25-2014, 02:40 PM
  5. Replies: 7
    Last Post: 08-04-2013, 03:41 PM
  6. Lookup or match function with multiple criteria giving different results
    By Mian USman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2013, 09:15 AM
  7. Replies: 2
    Last Post: 12-02-2011, 03:42 PM

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