Time and Date of Next Value in a 2 column search

[reswan]

Trying to create a formula to work on values in column E and column F

I want the formula to look at the value in column E and see where the value next appears historically in either column E or column F. On finding the next occurrence of the value I would like to read the time and date in column A and column B and record it in the row of the initial search value.
The time and date to be recorded should go in column G and column H respectively.

I would like to carry out the same operation on the values in column F. Again looking to see where the next value appears in column E or column F. On finding the next historical occurrence of the value I would like to read the time and date in column A and column B and this time record in the row of the initial search value but place the time and date in column I and column J respectively.

BEFORE

Time and Date of Next Value in a 2 column search.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Time			Value 1	Value 2	Time 1	Date 1	Time 2	Date 2
2	17:08	11/06/2019			watch	pen
3	18:05	10/06/2019			belt	umbrella
4	16:40	09/06/2019			pipe	shoes
5	16:03	08/06/2019			shoes	watch
6	14:28	07/06/2019			lighter	umbrella
7	14:03	06/06/2019			pen	belt
8	13:54	05/06/2019			umbrella	pipe
9	12:50	04/06/2019			watch	lighter
BEFORE

AFTER

Time and Date of Next Value in a 2 column search.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Time			Value 1	Value 2	Time 1	Date 1	Time 2	Date 2
2	17:08	11/06/2019			watch	pen	16:03	08/06/2019	14:03	06/06/2019
3	18:05	10/06/2019			belt	umbrella	14:03	06/06/2019	14:28	07/06/2019
4	16:40	09/06/2019			pipe	shoes	13:54	05/06/2019	16:03	08/06/2019
5	16:03	08/06/2019			shoes	watch			12:50	04/06/2019
6	14:28	07/06/2019			lighter	umbrella	12:50	04/06/2019	13:54	05/06/2019
7	14:03	06/06/2019			pen	belt
8	13:54	05/06/2019			umbrella	pipe
9	12:50	04/06/2019			watch	lighter
AFTER

NB: dates are UK format

thanks for any solutions. Will want to apply the formulas over several thousand rows.

cross posted at another site but although I advised the formula supplied worked , it didn't when copying down through several thousand rows.


link to cross post, as per forum rules, Time and Date of Next Value

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Time			Value 1	Value 2	Time 1	Date 1	Time 2	Date 2
2	17:08:00	11/06/2019			watch	pen	16:03:00	08/06/2019	14:03:00	06/06/2019
3	18:05:00	10/06/2019			belt	umbrella	14:03:00	06/06/2019	14:28:00	07/06/2019
4	16:40:00	09/06/2019			pipe	shoes	13:54:00	05/06/2019	16:03:00	08/06/2019
5	16:03:00	08/06/2019			shoes	watch			12:50:00	04/06/2019
6	14:28:00	07/06/2019			lighter	umbrella	12:50:00	04/06/2019	13:54:00	05/06/2019
7	14:03:00	06/06/2019			pen	belt
8	13:54:00	05/06/2019			umbrella	pipe
9	12:50:00	04/06/2019			watch	lighter
10
Results

Cell Formulas
Range	Formula
G2:H9	G2	=IFERROR(INDEX(A3:A$10,AGGREGATE(15,6,(ROW($E3:$E$10)-ROW($E3)+1)/($E3:$F$10=$E2),1)),"")
I2:J9	I2	=IFERROR(INDEX(A3:A$10,AGGREGATE(15,6,(ROW($E3:$E$10)-ROW($E3)+1)/($E3:$F$10=$F2),1)),"")

 

[Onebracketshort]

Thanks for the welcome and reply. These 2 formulas work perfectly for me on the 10 row sample. Provide the expected results exactly.

What adjustments do I need to make these 2 formulas work over several thousand rows? Sometimes the value search doesn't get a match for quite a few rows.

Should I can adjust the cell references A3A$10 to A3A$200 , $E3:$E$200 , $E3:$F$200 ?

What does the -ROW($E3)+1) in the formula do?

 

[Fluff]

You can adjust them like

=IFERROR(INDEX(A3:A$5000,AGGREGATE(15,6,(ROW($E3:$E$5000)-ROW($E3)+1)/($E3:$F$5000=$E2),1)),"")

This will work down to row 5,000

 

[Onebracketshort]

that's done the trick. top man Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.