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]

H2:

=index($b$1:$g$1,,match(1,1/($b2:$g2<>""),0))

i2:

=index($b$1:$g$1,,match(1,1/($b2:$g2<>""),1))

 

[ChicagoGuy]

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><colgroup><col span="7"><col><col></colgroup>[/TABLE]

 

[63falcondude]

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]

Hello and welcome to the forum.

The formulas given in post #2 are array formulas and should be entered using Ctrl Shift Enter instead of just Enter.

 

[FDibbins]

Maybe this?
[Table="width:, class:grid"][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]

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]

2​

[/td][td]MATHEW[/td][td]

2​

[/td][td]

2​

[/td][td]

8​

[/td][td]

4​

[/td][td]

9​

[/td][td][/td][td]6/1/2017[/td][td]6/5/2017[/td][/tr]

[tr][td]

3​

[/td][td]MARK[/td][td][/td][td][/td][td]

4​

[/td][td]

1​

[/td][td]

8​

[/td][td]

7​

[/td][td]6/3/2017[/td][td]6/6/2017[/td][/tr]

[tr][td]

4​

[/td][td]LUKE[/td][td][/td][td]

5​

[/td][td]

3​

[/td][td]

7​

[/td][td][/td][td][/td][td]6/2/2017[/td][td]6/4/2017[/td][/tr]

[tr][td]

5​

[/td][td]JOHN[/td][td]

4​

[/td][td][/td][td]

6​

[/td][td]

8​

[/td][td]

9​

[/td][td][/td][td]6/1/2017[/td][td]6/5/2017[/td][/tr]

[tr][td]

6​

[/td][td]RNGO[/td][td]

5​

[/td][td]

6​

[/td][td]

1​

[/td][td]

9​

[/td][td]

2​

[/td][td]

3​

[/td][td]6/1/2017[/td][td]6/6/2017[/td][/tr]
[/table]
H2=INDEX($B$1:$G$1,MATCH(TRUE,INDEX(($B2:$G2<>0),0),0))
I2=INDEX($B$1:$G$1,MATCH(0,B2:G2,-1))
both copied down as needed

 

[ChicagoGuy]

Ahh Thank you.

 

[ChicagoGuy]

Fdibbins Thank you that worked perfectly.

 

[FDibbins]

Happy to help

 

[Aladin Akyurek]

In H2 enter and copy down:

=INDEX($B$1:$G$1,MATCH(TRUE,INDEX(ISNUMBER(B2:G2),0),0))

In I2 enter and copy down:

=LOOKUP(9.99999999999999E+307,B2:G2,$B$1:$G$1)

 

[ChicagoGuy]

thank you aladin