Index & Match Help.

[scliollio]

If someone could help me on the below scenario, that would be greatly appreciated!

I have a list of locations that either pass or fail based on criteria. I have the pass/fail setup. Now I need a formula that will list me all of the locations which failed.

I have a formula that will run through the list and give me the first one that fails, but I cannot figure out how to make it continue searching through and list all.

My formula: =INDEX($A$6:$D$52,MATCH("OOT",$D$6:$D$52,0),1)

This actual list contains ~50 locations, typically 8 or so "fail". So I'm looking for basically all that fail in a column in consecutive rows.
[TABLE="width: 500"]
<tbody>[TR]
[TD]LOCATION[/TD]
[TD]Criteria 1[/TD]
[TD]Criteria 2[/TD]
[TD]Pass/Fail[/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD]10[/TD]
[TD]0.02[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]17[/TD]
[TD]0.15[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]9[/TD]
[TD]0.18[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD]Utah[/TD]
[TD]1[/TD]
[TD]0.09[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]3[/TD]
[TD]0.20[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]Alaska[/TD]
[TD]4[/TD]
[TD]0.11[/TD]
[TD]Fail[/TD]
[/TR]
</tbody>[/TABLE]

 

[Roderick_E]

Wouldn't a pivot table work better?

 

[AhoyNC]

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C	D
1	LOCATION	Criteria 1	Criteria 2	Pass/Fail
2	Alabama	10	0.02	Fail
3	Texas	17	0.15	Pass
4	California	9	0.18	Fail
5	Utah	1	0.09	Pass
6	New York	3	0.2	Pass
7	Alaska	4	0.11	Fail
8
9	Failed
10	Alabama
11	California
12	Alaska
13
Sheet

 

[scliollio]

I'm doing this for a rolling 12, and would have to create 12 pivot tables and constantly create a new pivot table each month, which is no big deal, but a formula would look much more clean.

 

[=ODIN=]

Or maybe add a column with a Year#/Month# value and pivot... I always try to avoid array calcs because they're slow and confusing to nearly everyone -- sorry if I misunderstood your request.