Formula help

[KatSlif]

Hello
Hoping you can help me with a formula

Scenario:

Id Date Location Location Start Date time Location End Date Time
123 09/01/2018 AAA 10/01/2018 06:30 12/01/2018 14:00
123 09/01/2018 BBB 09/01/201808:10 10/01/201806:30

I need help with a formula that looks at the Id and brings through the firstlocation the person was at using Location Start Date Time – in the abovescenario it would bring through location BBB.

Can this be done?
If you could let me know that would be great
Kind Regards
Katherine

 

[admiral100]

Hi,

Try this : An array formula , Ctrl+Shift+Enter NOT just Enter

J2 =INDEX(C2:C3,MATCH(MIN(D2:D3+E2:E3),D2:D3+E2:E3,0))

	A	B	C	D	E	F	G	H	I	J
1	Id	Date	Location Strat	Date	time	Location End	Date	Time
2	123	09/01/18	AAA	10/01/18	06:30		12/01/18	14:00		BBB
3	123	09/01/18	BBB	09/01/18	08:10		10/01/18	06:30

<colgroup><col span="3"><col><col span="2"><col><col span="4"></colgroup><tbody>
</tbody>

 

[KatSlif]

Hi,

Try this : An array formula , Ctrl+Shift+Enter NOT just Enter

J2 =INDEX(C2:C3,MATCH(MIN(D2:D3+E2:E3),D2:D3+E2:E3,0))

	A	B	C	D	E	F	G	H	I	J
1	Id	Date	Location Strat	Date	time	Location End	Date	Time
2	123	09/01/18	AAA	10/01/18	06:30		12/01/18	14:00		BBB
3	123	09/01/18	BBB	09/01/18	08:10		10/01/18	06:30

<tbody>
</tbody>

Hello

I have tried the above and it is bringing through the same value for every record?

 

[admiral100]

Hi,

Ctrl+Shift+Enter NOT just Enter

J2 =INDEX($C$2:$C$5,MATCH(MIN(IFERROR(1/($A$2:$A$5=A2)*($B$2:$B$5<>0)*($B$2:$B$5)+($E$2:$E$5),9^9)),$B$2:$B$5+$E$2:$E$5,0))

	A	B	C	D	E	F	G	H	I	J
1	Id	Date	Location Strat	Date	time	Location End	Date	Time
2	123	09/01/18	AAA	10/01/18	06:30		12/01/18	14:00		DDD
3	124	04/01/18	BBB	05/01/18	08:10		10/01/18	06:30		CCC
4	124	04/01/18	CCC	05/01/18	07:20		10/01/18	06:30		CCC
5	123	08/01/18	DDD	05/01/18	07:20		10/01/18	06:30		DDD

<colgroup><col span="3"><col><col span="2"><col><col span="4"></colgroup><tbody>
</tbody>