+ Reply to Thread
Results 1 to 9 of 9

unique matches based on multiple criteria

  1. #1
    Forum Contributor
    Join Date
    05-01-2018
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    117

    unique matches based on multiple criteria

    Thanks for help
    Attached Files Attached Files
    Last edited by adsako; 01-29-2020 at 03:12 AM.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: unique matches based on multiple criteria

    Hi,

    The formula might be right, however there are additional spaces in Column D after Non, similarly in different columns aswell.. Get rid of those and it should work
    Cheers!
    Deep Dave

  3. #3
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: unique matches based on multiple criteria

    Put this in Cell F7

    =IFERROR(INDEX(Table1[[Nom ]],SMALL(IF((Table1[Journée de la sappe]=$G$3)*(Table1[Interessé ? ]=$G$4)*(Table1[Contrat signé ? ]=$G$5)*(ROW(Table1[Journée de la sappe])-ROW(Table1[[#Headers],[Journée de la sappe]]))=0,"",(Table1[Journée de la sappe]=$G$3)*(Table1[Interessé ? ]=$G$4)*(Table1[Contrat signé ? ]=$G$5)*(ROW(Table1[Journée de la sappe])-ROW(Table1[[#Headers],[Journée de la sappe]]))),ROWS($F$7:F7))),"")
    ...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.

  4. #4
    Forum Contributor
    Join Date
    05-01-2018
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    117

    Re: unique matches based on multiple criteria

    Hi
    Ive tried ... unfortunately still shows error

  5. #5
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: unique matches based on multiple criteria

    Hi,

    Can you tell me what the expected output should look like?

  6. #6
    Forum Contributor
    Join Date
    05-01-2018
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    117

    Re: unique matches based on multiple criteria

    Hi

    what I would like to see is:
    - in column G list of the suppliers (pulled out from column A) who meet criteria mentioned in cell G3 and G4 and G5.
    as the result for instance:
    in case in cell G3 / G4 / G5 we got "non" / "non" / "non respectively my list shouldinclude only two suppliers mentioned in cell A11 and A39

    or as a second example --- in case there are "reduction" / "oui" / "non" in cell G3 / G4 / G5 respectively I should get a list of suppliers (from column A) listed in cell A8 / A18 / A19 (because this cells meet criteria)

    I do not want column G to be dependent on column F and list of suppliers listed there - this column/list was created to return list of the suppliers based on criteria in cell G3

    When I apply suggested formulas I get ERROR in return

    basically I do not know how to introduce MORE THAN ONE criteria into my lookup

    Hope this explanation helps

    thanks

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: unique matches based on multiple criteria

    Hi,

    Please see the file attached

    Formula used -

    =IFERROR(INDEX(Table1[[Nom ]],SMALL(IF(($G$3=Table1[Journée de la sappe])*($G$4=Table1[Interessé ? ])*($G$5=Table1[Contrat signé ? ])*(ROW(Table1[[Nom ]])-ROW(Table1[[#Headers],[Nom ]]))=0,"",($G$3=Table1[Journée de la sappe])*($G$4=Table1[Interessé ? ])*($G$5=Table1[Contrat signé ? ])*(ROW(Table1[[Nom ]])-ROW(Table1[[#Headers],[Nom ]]))),ROWS($G$7:G7)-ROWS($G$7)+1)),"")
    Commit using Ctrl+Shift+Enter
    Attached Files Attached Files
    Last edited by NeedForExcel; 01-28-2020 at 05:06 AM.

  8. #8
    Forum Contributor
    Join Date
    05-01-2018
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    117

    Re: unique matches based on multiple criteria

    Hi
    that works perfectly
    thank you

    I'm trying to understand now logic behind

    would LOOKUP formula work here? and how it might be used?

    thanks

  9. #9
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: unique matches based on multiple criteria

    would LOOKUP formula work here? and how it might be used?
    Hi,

    Vlookup cannot be used for this problem.. The usage of Vlookup is quite different from the requirement here..

+ 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. list of unique matches based on multiple criteria
    By adsako in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-26-2020, 11:39 AM
  2. Replies: 2
    Last Post: 03-03-2014, 10:03 PM
  3. Replies: 6
    Last Post: 03-06-2013, 04:02 AM
  4. 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
  5. Replies: 2
    Last Post: 07-19-2011, 04:08 PM
  6. Replies: 4
    Last Post: 06-14-2010, 09:26 AM
  7. trying to count unique occurences w/in data that matches multiple criteria
    By broro183 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2006, 08:11 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