How to find the date corresponding to other cells

[raweya99]

Hello,
how can I populate a formula for the yellow cells to get the start and finish dates based on the cells that has "yes"
question excel.jpg

appreciate it.

[ardross]

See here:

https://www.excelforum.com/excel-for...ml#post5682161

instead of >0, use "yes"

[raweya99]

thank you very much.
does it work if I have different numbers?

[AliGW]

Just use <>"" as the criterion.

[raweya99]

appreciate your prompt reply.
I am facing a challenge in doing it, when I put "yes" in the formula
=INDEX($BL$131:$HX$131,MATCH(TRUE,INDEX((BL136:HX136<>"yes"),"yes"),"yes"))
it ends up with #value

how can I solve this? the same is for the Lookup value for the oldest date

[AliGW]

Provide a workbook, please.

[raweya99]

it seems that I cant attach a workbook. is it because I am a new user?

[AliGW]

I asked for a workbook to be attached, not a link. Read the instructions in the yellow banner at the top of the page. As a mod, I am very well aware of what you can and can't do here, so why would I ask you to do something that wasn't possible?

[raweya99]

oh no sorry I did not mean it, but I was not aware of the yellow boxes. I have done it now. I would appreciate it if you can have a look at:

AU136 and AV 136 cells

[AliGW]

Row 136:

Start:

=INDEX($BL$131:$HX$131,MATCH("FW",$BL136:$HX136,0))

Finish:

=LOOKUP(2,1/(BL136:HX136="FW"),$BL$131:$HX$131)

[raweya99]

thank you very much for your support.

that worked perfectly.

[AliGW]

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.