Formula Help Please

[natheplas]

Hello,
I have a worksheet with Works Order Numbers in column A, then OP_No in column B. There are date and time data in column D.

Is there a formula I can use to look at the works order number, use the date column, to return the previous op no?

For example

WO123 OPNO1 20/03/2022 -
WO123 OPNO2 25/03/2022 OPNO1

Test1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	WO_No	Op_No	Concate	Completion Date	Op_Narr_No	WC_Code	Labr_Time_Taken	Setup_Time_Taken	Qty_Complete	Qty_Rejected	Qty_Scrapped	Completion_Ind_YN	WO	Previous Op	Previous qty
2	10009	10	10001 10	03/10/2008	0	200IN	0	0	1	0	0	Y	10001		0
3	10003	10	10003 10	06/08/2008	0	200IN	0	0	10	0	0	Y	10003		0
4	10010	10	10009 10	05/08/2008	0	200IN	0	0	6	0	0	Y	10009		0
5	80129	10	10010 10	06/08/2008	0	200IN	0	0	2	0	0	Y	10010		0
6	80206	10	10011 10	16/03/2009	0	200IN	0	0	4	0	0	Y	10011		0
7	80222	10	10020 10	30/10/2008	0	200IN	0	0	2	0	0	Y	10020		0
8	80203	10	10021 10	29/10/2008	0	200IN	0	0	32	0	0	Y	10021		0
9	10034	10	10022 10	14/10/2008	0	200IN	0	0	30	0	0	Y	10022		0
10	80070	10	10023 10	09/02/2009	0	200IN	0	0	16	0	0	Y	10023		0
11	80143	10	10027 10	28/01/2009	0	200IN	0	0	15	0	0	Y	10027		0
12	80202	10	10028 10	20/04/2009	0	200IN	0	0	50	0	0	Y	10028		0
13	80065	10	10029 10	02/10/2008	0	200IN	0	0	30	0	0	Y	10029		0
14	80134	10	10033 10	28/11/2008	0	200IN	0	0	20	0	0	Y	10033		0
15	10029	10	10034 10	30/09/2008	0	200IN	0	0	58	0	0	Y	10034		0
16	80107	10	10036 10	27/01/2009	0	200IN	0	0	8	0	0	Y	10036		0
17	80160	10	10191 10	27/05/2009	0	200IN	0	0	1	0	0	Y	10191		0
18	10001	10	10191 20	27/05/2009	0	200IN	0	0	11	0	0	Y	10191		0
19	80054	10	10191 30	08/07/2009	0	200IN	0	0	5	0	0	Y	10191		0
Sheet1

 

[bstory84]

Possible solutions based on your data posted.

deleteme1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	WO_No	Op_No	Concate	Completion Date	Op_Narr_No	WC_Code	Labr_Time_Taken	Setup_Time_Taken	Qty_Complete	Qty_Rejected	Qty_Scrapped	Completion_Ind_YN	WO	Previous Op	Previous qty	x	Lookup WO	Lookup OP	< This Date	Previous OP#
2	10009	10	10001 10	03/10/2008	0	200IN	0	0	1	0	0	Y	10001		0		80054	10	3/25/222	10
3	10003	10	10003 10	06/08/2008	0	200IN	0	0	10	0	0	Y	10003		0
4	10010	10	10009 10	05/08/2008	0	200IN	0	0	6	0	0	Y	10009		0
5	80129	10	10010 10	06/08/2008	0	200IN	0	0	2	0	0	Y	10010		0
6	80206	10	10011 10	16/03/2009	0	200IN	0	0	4	0	0	Y	10011		0
7	80222	10	10020 10	30/10/2008	0	200IN	0	0	2	0	0	Y	10020		0
8	80203	10	10021 10	29/10/2008	0	200IN	0	0	32	0	0	Y	10021		0
9	10034	10	10022 10	14/10/2008	0	200IN	0	0	30	0	0	Y	10022		0
10	80070	10	10023 10	09/02/2009	0	200IN	0	0	16	0	0	Y	10023		0
11	80143	10	10027 10	28/01/2009	0	200IN	0	0	15	0	0	Y	10027		0
12	80202	10	10028 10	20/04/2009	0	200IN	0	0	50	0	0	Y	10028		0
13	80065	10	10029 10	02/10/2008	0	200IN	0	0	30	0	0	Y	10029		0
14	80134	10	10033 10	28/11/2008	0	200IN	0	0	20	0	0	Y	10033		0
15	80054	10	10034 10	30/09/2008	0	200IN	0	0	58	0	0	Y	10034		0
16	80054	10	10036 10	27/01/2009	0	200IN	0	0	8	0	0	Y	10036		0
17	80160	10	10191 10	27/05/2009	0	200IN	0	0	1	0	0	Y	10191		0
18	10001	10	10191 20	27/05/2009	0	200IN	0	0	11	0	0	Y	10191		0
19	80054	10	10191 30	08/07/2009	0	200IN	0	0	5	0	0	Y	10191		0
Sheet1

Cell Formulas
Range	Formula
T2	T2	=INDEX(SORT(FILTER($B$1:$D$19,($A$1:$A$19=Q2)*($B$1:$B$19=$R$2)*($D$1:$D$19<S2)),3,-1),1,1)

 

[natheplas]

54

10

3/25/222

Hi bstory84,

When I copy your formula across I get a calc error.
Did you do anything with the data result to get the result you did?

I noticed your lookup data ended in 222 was this intentional?

Thanks,