Forum Discussion
How to extract cell information based on another cell entry
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 |
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 Replies
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:
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 https://support.office.com/en-gb/article/Keyboard-shortcuts-in-Excel-2016-for-Mac-acf5419e-1f87-444d-962f-4e951a658ccd
+ SHIFT + RETURN
or
CONTROL + SHIFT + RETURNUsing 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?
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))),"")}
