+ Reply to Thread
Results 1 to 4 of 4

Formula to show all items that match three criteria

  1. #1
    Forum Contributor
    Join Date
    07-22-2009
    Location
    Minneapolis, MN
    MS-Off Ver
    2016
    Posts
    220

    Formula to show all items that match three criteria

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    ITEM QTY LOCATION BIN LOC_TO_MATCH BIN_TO_MATCH ITEMS_MATCHED
    2
    Item 1
    -1
    Location 1 Bin 1 Location 3 Bin 2 Item 3
    3
    Item 2
    1
    Location 2 Bin 1 Item 5
    4
    Item 3
    -1
    Location 3 Bin 2 Item 7
    5
    Item 4
    1
    Location 3 Bin 2
    6
    Item 5
    -1
    Location 3 Bin 2
    7
    Item 6
    1
    Location 3 Bin 1
    8
    Item 7
    -1
    Location 3 Bin 2
    9
    Item 8
    1
    Location 4 Bin 1
    10
    Item 9
    -1
    Location 2 Bin 2
    11
    Item 10
    1
    Location 3 Bin 1


    Using the table above as an example, I am looking for help with a formula (probably array) for column "E". The column should list all of the items from column "A" that matches the following two criteria:

    Criteria 1: A negative value in column "B".
    Criteria 2: Column "C" match with cell "$E$2".
    Criteria 3: Column "D" match with cell "$F$2".

    The current values in column "G" are an example of what the array formula would return given the current values in "$E$2" & "$F$2".

    Thanks in advance for the help!

  2. #2
    Forum Contributor
    Join Date
    07-22-2009
    Location
    Minneapolis, MN
    MS-Off Ver
    2016
    Posts
    220

    Re: Formula to show all items that match three criteria

    Here is the array formula I have that checks for criteria 1 & 2 that would get entered in $G$2 and copied down...
    ={IFERROR(INDEX($A$2:$A$11,SMALL(IF($C$2:$C$11=$E$2,IF($B$2:$B$11<0,ROW($A$2:$A$11))),ROW(1:1))-1),"")}

    How would this get modified to accommodate the 3rd criteria?
    Last edited by Rerock; 11-17-2014 at 12:22 PM.

  3. #3
    Forum Contributor
    Join Date
    07-22-2009
    Location
    Minneapolis, MN
    MS-Off Ver
    2016
    Posts
    220

    Re: Formula to show all items that match three criteria

    bumpity bump bump

  4. #4
    Forum Contributor
    Join Date
    07-22-2009
    Location
    Minneapolis, MN
    MS-Off Ver
    2016
    Posts
    220

    Re: Formula to show all items that match three criteria

    Okay,
    I figured it out; here is the formula:

    =IFERROR(INDEX($A$2:$A$11,MATCH(0,IF($D$2:$D$11<>$F$2,"",IF($D$2:$D$11>0,"",COUNTIF(A$1:$A1,$A$2:$A$11))),)),"")

+ 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] Formula to show all items that match two criteria?
    By Rerock in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-12-2014, 10:35 AM
  2. Show Difference of sheet1-sheet2 in sheet3 if items match?
    By nyichiban in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-23-2013, 04:03 PM
  3. List items to new sheet which match specific criteria
    By Toonces in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-25-2013, 02:14 PM
  4. Show info in Column C, if A & B match criteria
    By PowerSchoolDude in forum Excel General
    Replies: 4
    Last Post: 08-21-2009, 03:02 PM
  5. Hide/show specific Pivot Table Items based on multiple criteria
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-18-2009, 05:15 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