Combine ADRESS and VLOOKUP

[ChicagoGuy]

I have a set of people that are forecasting their expected work completion similar to what i have listed below. You will notice in coumns H and I have a start date and end date. I actually have a few hundred rows in real life and based on various business events their start and end date may change.

Is there a way to find the first and last value in a row and then use that to pull the date from A1:F1? Seems like some sort of combinationof address and maybe vlookup but Im a bit lost.

For example I would like to have H2 say 6/2/2017 and I2 say 6/6/2017. But i cant figure out how to connect the dots.

Hopefully I am making sense. Please just ask for clarfication if you need it.

[TABLE="width: 776"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Dates[/TD]
[TD]6/1/2017[/TD]
[TD]6/2/2017[/TD]
[TD]6/3/2017[/TD]
[TD]6/4/2017[/TD]
[TD]6/5/2017[/TD]
[TD]6/6/2017[/TD]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]MATHEW[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]MARK[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]LUKE[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]JOHN[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]RNGO[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col><col span="7"><col><col></colgroup>[/TABLE]

 

[cerfani]

thank you cerfani.

I am getting an #value error though. I see where you are going. let me see if i can figure out whats throwing the error

[TABLE="width: 712"]
<tbody>[TR]
[TD]Dates[/TD]
[TD]6/1/2017[/TD]
[TD]6/2/2017[/TD]
[TD]6/3/2017[/TD]
[TD]6/4/2017[/TD]
[TD]6/5/2017[/TD]
[TD]6/6/2017[/TD]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[/TR]
[TR]
[TD]MATHEW[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

sorry for the late reply, i tested it earlier and it seemed to work ok but I guess you got it working regardless