Index into sheet by row value and column value and then find column name

Question

I have an excel sheet that has the following format

 Name        1/1/2018  1/8/2018   1/15/2018   1/23/2018   1/30/2018
project1                 dev        dev
project2                            dev         dev        dev
project3       dev       dev

I am trying to create a query where I provide the Name field and it returns me the column name (a date string) corresponding to the first instance of "dev" in the row associated with that name. And I'm trying to create another query that would return the last instance of "dev" in that row.

For example, for project1, the first query would return 1/8/2018 and the second would return 1/15/2018. For project2 the first query would return 1/15/2018 and the second would return 1/30/2018.

I've gotten as far as this, which I thought should find the column index of at least one of the "dev" in the project2 row, but even this doesn't work, and even if it did I'm not sure how to go about finding the first "dev" versus the last "dev" and how to get the column name.

=MATCH("dev", INDEX(A1:F100, MATCH("project2",A:A,0), 0))

If anyone has any pointers I'd really appreciate it. I'm just learning excel and don't have a grasp of the more advanced syntax yet.

Thanks!

Answer 1

For the first and drag down:

=INDEX($B$1:$F$1,MATCH("dev",$B2:$F2,0))

For the last and drag down:

{=INDEX($B$1:$F$1,MAX(IF($B2:$F2="dev",COLUMN($B2:$F2)-COLUMN(INDEX($B2:$F2,1,1))+1)))}

Use ctrl + shift + enter on the last one to enter as an array formula and drag down

Reference:

https://exceljet.net/formula/get-last-match

Edit:

OP has simplified latter into:

{=INDEX($B$1:$F$1,MAX(IF($B2:$F2="dev",COLUMN($B2:$F2)-COLUMN($B2)+1)))} 

Answer 2

Assuming the dates are sorted ascending, as shown in your example:

First match:

=AGGREGATE(15,6,1/(INDEX($A$2:$F$4,MATCH($A8,$A$2:$A$4,0),0)="dev")*$A$1:$F$1,1)

Last match:

=AGGREGATE(16,6,1/(INDEX($A$2:$F$4,MATCH($A8,$A$2:$A$4,0),0)="dev")*$A$1:$F$1,1)

or

=LOOKUP(2,1/(INDEX($A$2:$F$4,MATCH($A8,$A$2:$A$4,0),0)="dev"),$A$1:$F$1)

If it happens that the dates, or column headers, are unsorted, or that the column headers are text and not real dates, try this:

First match:

=INDEX($A$1:$F$1,1,AGGREGATE(15,6,1/(INDEX($A$2:$F$4,
MATCH($A8,$A$2:$A$4,0),0)="dev")*COLUMN($A$1:$F$1),1))

Last Match

=INDEX($A$1:$F$1,1,AGGREGATE(16,6,1/(INDEX($A$2:$F$4,
MATCH($A8,$A$2:$A$4,0),0)="dev")*COLUMN($A$1:$F$1),1))

or

=LOOKUP(2,1/(INDEX($A$2:$F$4,MATCH($A8,$A$2:$A$4,0),0)="dev"),$A$1:$F$1)