Lookup certain value in row and give back cell value in same column first row

[CaptnAbraham]

Hi everyone,

I'm at an impass and can't get my head around a certain task.
I have a table (E5:NS154) where the cells are either empty, contain "in progress" or "finished".
The rows can contain "in progress" several times but have "finished" only once.

In the very first row (E1:NS1) I have certain dates set (basically every monday for the foreseeable future).

Now, I would like to lookup the rows, find the first cell (or rather column) with "in progress" in it and give back the date value of that column (always in first row) in the C column.
Same same for something I'm looking for in column D, which would be "finished". Same here, find the text and get me the value of row 1 of that column where it is.

I've been trying to combine Lookup and Index and whatnot but fail at every turn...

I would appreciate any help or pointers.

Thanks

 

[Fluff]

How about

=INDEX($E$1:$NS$1,MATCH("In progress",E2:NS2,0))

 

[CaptnAbraham]

Thanks! Yes!
It's the posterboy case of not seeing the forest for the trees... I was so sure I needed to use LOOKUP in whatever way of combination available that the simple answer escaped me.

I've added an IFERROR() infront just to be on the safe side, therefore the end result looks like this:

IFERROR(INDEX($E$1:$NS$1, MATCH("in progress", $E5:$NS5,0)),"")

Thank you for the help!

Cheers~

 

[Fluff]

You're welcome & thanks for the feedback.

 

[CaptnAbraham]

Hello again,

I'm not sure if I can reactive this thread once marked "solved", but I would nevertheless try and slightly change the details of the detailed problem from before, adding two images of the two sheets of the file.

Sheet 1: marking the weeks in which some tasks are to be done (previously it was diferentiated between "in progress" and "finished", this time a simple x marks the week in general). The calender weeks in row 4 are of course called down from above using WEEKNUM(I1,21) etc.

Sheet 2: other things that are happening here but I want to focus on columns A and B, Start "date" and End "date".

I would like to get the date from row 1 sheet 1 (the Mondays) into column A of sheet 2 for when the first "x" is set in that row...
and I would like to get the date from row 2 sheet 1 (the Fridays) into column B of sheet 2 for when the last "x" is set in that row.

I can achive it easily with XMATCH in 365, but not in the "offline" version of 2019, which I ultimately have to use. It should be doable using the {} ARRAY function I imagine but I'm again... at a loss...

Much appreciation towards anyone with insightful ideas and/or solutions~

Thanks

 

[Fluff]

For the last x you can use

=LOOKUP(2,1/(Sheet1!B5:K5="x"),Sheet1!$B$2:$K$2)

 

[CaptnAbraham]

Once again a simple and expectedly easy (and also easy on the eyes) solution.

Many thanks again from a simple and lowly member.
A great community is... just a great community.

Thank you.

 

[Fluff]

Glad to help & thanks for the feedback.