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.

LOCATION	Criteria 1	Criteria 2	Pass/Fail
Alabama	10	0.02	Fail
Texas	17	0.15	Pass
California	9	0.18	Fail
Utah	1	0.09	Pass
New York	3	0.20	Pass
Alaska	4	0.11	Fail

<tbody>
</tbody>

 

[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.