How to extract cell information based on another cell entry

[Benjamin Davis]

I have an excel sheet with the following layout: My goal is to find each occurrence of a machine number that has a downtime equal to or greater than 4 hours and put that info Plus the reason into a separate table. I want to search by machine number find downtime =>4 and return each occurrence of the machine number, downtime and reason to a separate table to be used as a chart? I have tried MATCH, VLOOKUP, INDEX, MATCH but no joy as yet. Any ideas or help is greatly appreciated.

Machine #	Run Number	Downtime	Reason for down time
1	x0	0.50	blah
2	xx0	3.80	blah blah
1	xxx0	7.50	blah blah blah
4	x000	2.10
5	xxxx	1.40
1	x0000	10.50	blah boo
7	0000x	4.00	boo blah
Machine #	Downtime > 4 hrs	Reason
1	7.50	blah blah blah
1	10.50	blah boo
7	4.00	boo blah

[#2]

Try using this array formula:

=IFERROR(INDEX(A$2:A$8,SMALL(IF($C$2:$C$8>=4,ROW(A$2:A$8)-ROW(B$2)+1),ROWS(B$2:B2))),"")

Confirm with CTRL + SHIFT + ENTER and drag over to the right and then down

I've also attached an example file:

[#3]

Thank you soooo! much Mike. It works great in the example. Now the problem is I am on MacOS High Sierra 10.13.3 with Excel for Mac 2016 and according to Office Support

+ SHIFT + RETURN
or
CONTROL + SHIFT + RETURN

Using this key combination should allow me to enter as an array formula, however, I have a no joy condition again, not to mention dragging to the right and down. Andy ideas?

[#4]

Try Ctrl + Shift + Return

When you enter the formula correctly "curly braces" will show around the formula.

{=IFERROR(INDEX(A$2:A$8,SMALL(IF($C$2:$C$8>=4,ROW(A$2:A$8)-ROW(B$2)+1),ROWS(B$2:B2))),"")}

[#5]

I am stuck here: this is the concept and formula thus far.

I have a table as structured in the screenshot. I want to extract the Line #, Downtime =>4, and Reason to a separate table to create a chart by line number. Using this formula {=IFERROR(INDEX(Line,SMALL(IF(Downtime=>E4,ROW(Line)-MIN(ROW(Reason))+1),ROWS($E$5:E5))),"")}. Just can't quite make it work. Could you offer suggestions

The difference in the first suggestion maybe because I did not represent my true worksheet layout.

[#6]

Your screenshot shows up really small and is blurry when I enlarge it.  Is there anyway you can provide an example workbook with the same setup?  Please provide non-sensitive data.

[#7]

So in as few words as possible. This sheet will be used as a template and gets created weekly. I want to extract; Machine Line #, Downtime  =>4, and cause data cells into a table or pivot chart that can be used to show downtime by machine and reason.

See my example charts.

One thing I did was highlight the data for the week then apply a filter of =>4, then copied that data to make the chart you can see. I am thinking there is a better way. 

[#8 · Accepted solution]

Ben-

Try this file.  I updated the array formula and added a dynamic named range that adjusts based on the array formula.  It looks at rows W2:W41 (i.e. 40 cells) and adjusted the range based on the difference:

myPivotRange

=OFFSET('Weekly Template'!$W$2,0,0,40-COUNTBLANK('Weekly Template'!$W$2:$W$41),3)

[#9]

Thank you so much for your continued assistance. This looks great so now to try and understand how each portion of the array formula works and this is the first I ever heard of a myPivotRange. So I shall try and see how it all works. But I believe you have solved the immediate need. Thank you again.

[#10]

Glad to help!  Just be aware that myPivotRange isn't a standard Range.  It's a named range that I created to fit your needs.  If you want to know more about named ranges check out one of these links:

https://exceljet.net/lessons/how-to-create-a-named-range

http://www.contextures.com/xlNames01.html