Formula (array?) to list unique entries that match criteria?

In D2 enter this array formula and copy down

=IFERROR(INDEX($C$2:$C$11,MATCH(0,IF(B$2:B$11>0,"",COUNTIF(D$1:$D1, $C$2:$C$11)),)),"")

...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. Press F2 on that cell and try again.

Row\Col	A	B	C	D
1	ITEM	QTY	LOCATION
2	Item 1	-1	Location 1	Location 1
3	Item 2	1	Location 2	Location 3
4	Item 3	-1	Location 3	Location 2
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

Thank you for the feedback!

just change D$1:$D1 to E$1:E1

Array entered

=IFERROR(INDEX(A$2:A$11,MATCH(0,IF(B$2:B$11>0,"",COUNTIF(E$1:$E1,A$2:A$11)),)),"")

Originally Posted by AlKey

Array entered

=IFERROR(INDEX(A$2:A$11,MATCH(0,IF(B$2:B$11>0,"",COUNTIF(E$1:$E1,A$2:A$11)),)),"")

That seems to list all of the items in column "A", that have a negative value in column "B"...

Can we have it match cell $D$2 with column "C", AND has a negative value in column "B"?

It lists only those that meat criteria for the locations listed in column D.

The result is

Item 1
Item 3
Item 5
Item 7
Item 9

Location 1

Item 1

Location 2

Item 9

Location 3

Item 3
Item 5
Item 7

Row\Col	A	B	C	D	E	F
1	ITEM	QTY	LOCATION	LOC_RESULTS	LOC_TO_MATCH	ITEMS_MATCH
2	Item 1	-1	Location 1	Location 1	Location 3	Item 3
3	Item 2	1	Location 2	Location 3		Item 5
4	Item 3	-1	Location 3	Location 2		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

Okay,
I'm using this as a form generator, and the table above is close to how I will be using the data. I NEED the array formula in column F to list unique items from column "A" that meet two criteria:
1) if the value in column "C" matches cell $E$2
2) if the value in column "B" is a negative value.

This is the final hurdle, then the rest of this form generator is smooth sailing.

Thanks for all of you help so far, I really appreciate it!

Not quite sure what you need at this point. If you want to extract items for locations listed in D to columns to the right you can do this with this array formula:

in E2 and copy to the right and then down.

=IFERROR(INDEX($A$2:$A$11,SMALL(IF(($C$2:$C$11=$D2)*($B$2:$B$11<0),ROW($C$2:$C$11)-ROW($E$2)+1),COLUMNS($E2:E2))),"")

...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. Press F2 on that cell and try again.

Row\Col	A	B	C	D	E	F	G
1	ITEM	QTY	LOCATION	Locations	Items
2	Item 1	-1	Location 1	Location 1	Item 1
3	Item 2	1	Location 2	Location 3	Item 3	Item 5	Item 7
4	Item 3	-1	Location 3	Location 2	Item 9
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