Lookup specific values that occur in sequence

[estephenkim]

In a data set, I'm trying to identify a way to lookup the first instance of a transaction type and its corresponding date and the first instance of a second transaction type (and corresponding date) that occurs after the first transaction type. In the enclosed example (click on image), in the yellow highlighted cells to the right, I'd like to apply a formula (or other method) to identify the first Initial transaction and date and first Pass (and date) that occurs after the first Initial (the required data are highlighted in green and orange). Any viable suggestions or ideas would be appreciated!

 

[awoohaw]

try this:

Mr Excel Akbarov.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Transaction 1	Transaction 1 daate	Transaction 2	Transaction 2 Date	Transaction 3	Transaction 3 date	Transaction 4	Transaction 4 Date		First Initial	Date of First Initial	First Pass	Date of First Pass
2	Initial	2021-09-15	Verify	2021-10-01			Pass	2022-11-11		Transaction 1	2021-09-15	Transaction 4	2022-11-11
3	Initial	2022-06-24	Verify	2022-08-28	Fail	2022-09-29	--			Transaction 1	2022-06-24
4	Initial	2022-03-26	Verify	2022-06-01	Pass	2022-07-05	--			Transaction 1	2022-03-26	Transaction 3	2022-07-05
5	Initial	2021-12-16	Pass	2022-01-05	Initial	2022-09-15	Pass	2022-09-20		Transaction 1	2021-12-16	Transaction 2	2022-01-05
6	Verify	2022-01-05	Pass	2022-03-16	Initial	2022-08-30	Pass	2022-08-31		Transaction 3	2022-08-30	Transaction 2	2022-03-16
estephenkim

Cell Formulas
Range	Formula
J2:J6	J2	=IFNA(INDEX($A$1:$H$1,MATCH("Initial",$A2:$H2,0)),"")
K2:K6	K2	=IFNA(INDEX($A2:$H2,MATCH("Initial",$A2:$H2,0)+1),"")
L2:L6	L2	=IFNA(INDEX($A$1:$H$1,MATCH("Pass",$A2:$H2,0)),"")
M2:M6	M2	=IFNA(INDEX($A2:$H2,MATCH("Pass",$A2:$H2,0)+1),"")

 

[estephenkim]

Thanks for your efforts, awoohaw! I think you're taking the right approach with this formula. I just need to add an additional element to select the first instance of a Pass that occurs after the Initial.

For example, in row 6, the formula as it is currently written returns the first Pass in that row, dated 2022-03-16 (Cells C6:D6). The correct Pass I need in row 6 is the one that follows the Initial, located in Cells G6:H6. Said differently, I need the first Pass transaction that occurs after the Initial transaction in each row.

Is there a way to modify the formula for the Pass transaction to anchor it to the Initial transaction in a given row? I wonder if a combination of INDEX and XLOOKUP might work?