0

I am trying to get a date from A5:A999 to appear in L3 if the word Fail appears in column K5:k999.

Column A will have a different date in each cell of the column. Column K will either have nothing or the word "Fail" in each cell of the Column. L3 is the target cell I want the date from A5 to appear if the word fail appears in the cell K. There will be five rows like this in a set and each set of five will repeat with a different date in column A and possible blank or fail in column k

A5          B5         C5        D5   E5   F5    G5    H5   I5  J5    K5              L3
9/5/2020  master    near gate    no   no   8.7   0.0   0.0            Fail           9/5/2020

IMG:

Sample Data

8/20/2020 | Master     |
7/31/2020 | Camera     |
8/10/2020 | Regular 1  |
8/10/2020 | Regular 2  |
8/20/2020 | Flash      |
8/08/2020 | Master     |
8/08/2020 | Camera     |
8/20/2020 | Regular 1  |
8/20/2020 | Regular 2  |
8/20/2020 | Flash Only |
Improve this question
10
  • Can you possibly show a larger snapshot? E.g. Set A5:A10 are dates in A5:A10 same or different? Does word Fail if appears so, anywhere in K5:K10 within a set of 5? Does L in a set supposed to show only one date or all, if one should it be the first row in that set? – patkim Sep 8 '20 at 13:38
  • 1
    Sounds a bit like a lookup - assuming there's only one 'Fail' in the whole sheet? What if there are more than one fail, where do the other dates go if J3 is already taken? – Spencer Barnes Sep 8 '20 at 13:58
  • =IFERROR(INDEX(A5:$A$999,MATCH("Fail",$K$5:$K$999,0)),"") will give you the date for the first time 'Fail' appears. For a more complete answer, edit your question to add the information requested. – Alex M Sep 8 '20 at 16:10
  • No, there could be multiple "fail" in the sheet. Every 5th row is the same unit being measured. So, if unit one fails at any one of the hundreds of readings I want the date of the fail to be printed in another cell. I want the date for this unit in L3. 2nd unit 's info is in A6, third is in A7, so on and so on. if K6 is fail the date of faileur would go into L4, so on and so on. – Jason Wozniak Sep 8 '20 at 17:38
  • The date for the first fail is 7/31/2020 – Jason Wozniak Sep 8 '20 at 17:39
1

You will need to adjust the references to suit your needs.

The following formula will pull the date from any row that has the word "Fail" in the same row.

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($C$6:$C$34)/($C$6:$C$34="FAIL"),ROW(A1))),"")

The above formula is place in E6 of the example and copied down. The aggregate function performs array like operation without being an array formula. As a result, avoid using full column references within the aggregate function. Drag the formula down as needed. Excessive use or large ranges within the formula may result in slow performance.

enter image description here

If you only need the date from the yellow rows, I would suggest adding a helper column that will flag the yellow row as failed if something in the previous 4 rows is failed. Reference that helper column when checking for failure flags.

Improve this answer
11
  • Forward Ed, Thank you for your guidance, but could you dumb it down for me? – Jason Wozniak Sep 14 '20 at 14:57
  • Forward Ed, Thank you for your guidance, but could you dumb it down for me? Row A4,A9,A14,A20 so on so on for hundreds of rows are all the same unit. If fail is in column K I want the date from that row to appear in column cell L20. Row A5,A10,A15, A21 are a different unit and I want the date from a put into L21 if fail is in k. It will be this way for 5 different units. I know I don't fully understand your answer, and plugging it into my sheet did not work. I would not even begin to know how a helper column works. Thank you for any further assistance. – Jason Wozniak Sep 14 '20 at 15:06
  • Please mock up a small sample set of data with expected results. Sounds like you need 3 columns: date, unit name, fail status. And an expected results. Edit your original post to include this info. – Forward Ed Sep 14 '20 at 16:22
  • Been reading comments and find the wording confusing. when you refer to UNITS that can be unit A, B, or C or any combination of unit names. hard to understand if at times you are talking about a unique unit such as just unit A or talking about all units. Hence why I asked for the data mock up earlier. I also currently do not understand what roll the group of 5 units (A,B,C,D,E) has to play in getting the list of failure dates. The solution above is just a list of all failure dates. It does not care about which unit (A, B, C or D) the date belonged to. – Forward Ed Sep 14 '20 at 16:44
  • Are you trying to get a sequential list of failure dates grouped by unit name? ie list all the unit A failures, then list all the unit B failures etc? – Forward Ed Sep 14 '20 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.