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.

	A	B	C	D	E	F	G	H	I
1	Dates	6/1/2017	6/2/2017	6/3/2017	6/4/2017	6/5/2017	6/6/2017	START DATE	END DATE
2	MATHEW	2	2	8	4	9
3	MARK			4	1	8	7
4	LUKE		5	3	7
5	JOHN	4		6	8	9
6	RNGO	5	6	1	9	2	3

<tbody>
</tbody><colgroup><col><col span="7"><col><col></colgroup>

 

[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

Dates	6/1/2017	6/2/2017	6/3/2017	6/4/2017	6/5/2017	6/6/2017	START DATE	END DATE
MATHEW	2	2	8	4	9		#VALUE!	#VALUE!

<tbody>
</tbody><colgroup><col span="7"><col><col></colgroup>

 

[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

Dates	6/1/2017	6/2/2017	6/3/2017	6/4/2017	6/5/2017	6/6/2017	START DATE	END DATE
MATHEW	2	2	8	4	9		#VALUE!	#VALUE!

<tbody>
</tbody>

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?

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Dates	6/1/2017	6/2/2017	6/3/2017	6/4/2017	6/5/2017	6/6/2017	START DATE	END DATE
2​	MATHEW	2​	2​	8​	4​	9​		6/1/2017	6/5/2017
3​	MARK			4​	1​	8​	7​	6/3/2017	6/6/2017
4​	LUKE		5​	3​	7​			6/2/2017	6/4/2017
5​	JOHN	4​		6​	8​	9​		6/1/2017	6/5/2017
6​	RNGO	5​	6​	1​	9​	2​	3​	6/1/2017	6/6/2017

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