Display start and end of tasks

[JVladev]

Hello,

If anyone can help, I have a table of tasks and a row showing days, and below that row are rows with the completion of tasks, which is marked with an "x". How to display information in column "B" with start day and in column "C" with end day for the given task.
As an example, I am attaching a photo in which the columns in which I want to extract the information with formulas are marked in yellow, and I have given the manually entered data for the first two tasks as an example.

Thanks!

[#2]

@JVladev 

Hello!

Use this on B5 (Start date):

=XLOOKUP("X",D5:AQ5,D3:AQ3,,,1)

On C5 (Finish date) use this:

=XLOOKUP("X",D5:AQ5,D3:AQ3,,,-1)

Change cell (absolute) references accordingly and drag down each of these. Also change "X" based on if it is in lower or upper case.

Hope it works for you.

Martin

[#3]

@MAngosto Thanks, but I'll have to look for another function because I don't have XLOOKUP. I tried with LOOKUP it displays the end day, but if I do more than one break and it doesn't display the end day correctly.

If you have any idea how to adjust the function or other function or combination of functions to output the correct start and end days.

[#4]

@JVladev 

=IF(COUNTIF(D5:AQ5, "x"), INDEX($3:$3, MIN((D5:L5="x")*COLUMN(D5:L5))), "")

and

=IF(COUNTIF(D5:AQ5, "x"), INDEX($3:$3, MAX((D5:L5="x")*COLUMN(D5:L5))), "")

Confirm the formulas by pressing Ctrl+Shift+Enter to turn them into array formulas.

[#5]

@MAngosto 

If your formula doesn't cut it with the OP, this can only be worse 

= LET(
    MINIFXλ, LAMBDA(task, MINIFS(day, task, "x")),
    MAXIFXλ, LAMBDA(task, MAXIFS(day, task, "x")),
    HSTACK(
      BYROW(active, MINIFXλ),
      BYROW(active, MAXIFXλ)
    )
  )

[#6]

@HansVogelaar  Thanks!

It worked for the final day, but it doesn't display correctly for the initial day. I also tried by changing it to not read from the whole 3rd row, but only from the range, but again it's not right.

[#7 · Accepted solution]

@JVladev 

Sorry, I was careless. Try these (again confirmed by pressing Ctrl+Shift+Enter):

=IF(COUNTIF(D5:AQ5, "x"), INDEX($3:$3, MIN(IF(D5:AQ5="x",COLUMN(D5:AQ5)))), "")

=IF(COUNTIF(D5:AQ5, "x"), INDEX($3:$3, MAX(IF(D5:AQ5="x",COLUMN(D5:AQ5)))), "")

[#8]

@HansVogelaar  Thanks! Excellent