# Formula to show all items that match two criteria?

1. ## Formula to show all items that match two criteria?

 Row\Col A B C D E 1 ITEM QTY LOCATION LOC_TO_MATCH ITEMS_MATCH 2 Item 1 -1 Location 1 Location 3 Item 3 3 Item 2 1 Location 2 Item 5 4 Item 3 -1 Location 3 Item 7 5 Item 4 1 Location 3 6 Item 5 -1 Location 3 7 Item 6 1 Location 3 8 Item 7 -1 Location 3 9 Item 8 1 Location 4 10 Item 9 -1 Location 2 11 Item 10 1 Location 3

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 "\$D\$2".

The current values in column "E" are an example of what the array formula would return given the current value in "\$D\$2".

Thanks in advance for the help!

2. ## Re: Formula to show all items that match two criteria?

in e2 this array entered formula
=IFERROR(INDEX(\$A\$2:\$A\$11,SMALL(IF(\$C\$2:\$C\$11=\$D\$2,IF(\$B\$2:\$B\$11<0,ROW(\$A\$2:\$A\$11))),ROW(1:1))-1),"")

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

Beautifully done; thank you!!!

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