# Formula to show all items that match three criteria

1. ## 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. ## 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?

3. ## Re: Formula to show all items that match three criteria

bumpity bump bump

4. ## 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))),)),"")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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