Index match with an additional condition - list comparison

[willow1985]

I am not sure how to even begin modifying the below formula to achieve what I require.

I have a formula in column B that returns a result of No if there is no match in list 3, however I want it to also provide a Yes result if a record with the same date appears in both List 1 and List 2

I would like it that record U-39 returned a "Yes" since the record + Date combination appears in List 1 and List 2

Is it possible to modify the formula to do this?

Stamp Signature Spreadsheet Uniflyte.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	List 1	Reassigned	DATE			List 2 - Broken	DATE				List 3	Available
2	UI-30	No	6-Jun-16			UCI-70	14-Aug-19				UCI-059	Yes
3	UI-33	No	26-May-16			UI 48	22-Dec-17				UCI-064	Yes
4	UI-39	No	15-Mar-21			UI 50	1-Jun-17				UCI-072	Yes
5	UI-41	No	3-Sep-20			UI-29	29-Nov-17				UCI-074	Yes
6	UI-42	No	12-Jul-19			UI-39	15-Mar-21				UCI-076	Yes
7	UI-43	No	8-Jun-18			UI-57	9-Jan-20				UCI-077	Yes
8						UI-63	29-Nov-17				UCI-080	Yes
9											UCI-082	Yes
10											UCI-083	Yes
11											UCI-084	Yes
12											UCI-086	Yes
13											UCI-087	Yes
14											UCI-088	Yes
15											UCI-089	Yes
16											UCI-090	Yes
17											UCI-092	Yes
18											UCI-73	Yes
19											UI 49	Yes
20											UI-25	Yes
21											UI-27	Yes
22											UI-31	Yes
23											UI-32	Yes
24											UI-40	Yes
25											UI-45	Yes
26											UI-5	Yes
27											UI-50	Yes
28											UI-57	Yes
29											UI-58	Yes
30											UI-62	Yes
31											UI-65	Yes
32											UI-7	Yes
33											UI-92	Yes
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=IF(A2<>"",IFERROR(INDEX(L:L,MATCH(A3,K:K,FALSE)),"No"),"")

 

[kweaver]

Why does your formula look at A3 in the MATCH when it seems to me you would be looking at A2 ??

Would this work in B2 (and filled down)?

=IF(A2<>"",IF(OR((A2=$F$2:$F$8)*(C2=$G$2:$G$8)),"YES",IFERROR(INDEX(L:L,MATCH(A2,K:K,FALSE)),"No")),"")

 

[Fluff]

How about

=IF(A2<>"",IF(C2=IFNA(INDEX($G$2:$G$8,MATCH(A2,$F$2:$F$8,0)),0),"Yes",IFERROR(INDEX(L:L,MATCH(A2,K:K,FALSE)),"No")),"")

 

[willow1985]

How about

=IF(A2<>"",IF(C2=IFNA(INDEX($G$2:$G$8,MATCH(A2,$F$2:$F$8,0)),0),"Yes",IFERROR(INDEX(L:L,MATCH(A2,K:K,FALSE)),"No")),"")

On my test sheet both formulas worked fine however on the real document I am getting a circular reference. To be honest I cannot see what is tripping it up and hope either of you might be willing to see where I made an error. I apologize for the complexity

Stamp Signature Spreadsheet Uniflyte.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Stamps Labeled as Returned	Stamps Reassigned (Y/N)	Date Stamp Returned	Stamps Available for Issue (TRUE/FALSE)	Stamps in 6 month Dormant Cycle (TRUE/FALSE)	Stamp Broken and available for re-order		Stamps listed as LOST			Stamps Re-assigned with no Return Date
2	UCI 044	No	6-Oct-16	TRUE	FALSE	UCI-70	14-Aug-19	UCI 043			UCI-059	Yes
3	UCI 054	No	21-Jul-16	TRUE	FALSE	UI 48	22-Dec-17	UCI 045			UCI-064	Yes
4	UCI 055	No	30-Jun-20	TRUE	FALSE	UI 50	1-Jun-17	UCI 051			UCI-072	Yes
5	UCI 056	No	2-Apr-22	FALSE	TRUE	UI-29	29-Nov-17	UCI 060			UCI-074	Yes
6	UCI 058	No	29-Nov-17	TRUE	FALSE	UI-39	15-Mar-21	UCI 061			UCI-076	Yes
7	UCI 064	No	29-Sep-17	TRUE	FALSE	UI-57	9-Jan-20	UCI-049			UCI-077	Yes
8	UCI 62	No	27-Jun-16	TRUE	FALSE	UI-63	29-Nov-17	UCI-052			UCI-080	Yes
9	UCI-044	No	10-Jun-20	TRUE	FALSE			UCI-053			UCI-082	Yes
10	UCI-046	No	1-Jan-21	TRUE	FALSE			UCI-055			UCI-083	Yes
11	UCI-061	No	28-Aug-20	TRUE	FALSE			UCI-058			UCI-084	Yes
12	UCI-065	No	9-Nov-18	TRUE	FALSE			UCI-068			UCI-086	Yes
13	UCI-067	No	2-Feb-20	TRUE	FALSE			UCI-071			UCI-087	Yes
14	UCI-074	Yes						UCI-077			UCI-088	Yes
15	UCI-076	Yes						UCI-078			UCI-089	Yes
16	UCI-079	No	15-Sep-20	TRUE	FALSE			UCI-079			UCI-090	Yes
17	UCI-081	No	17-May-21	TRUE	FALSE			UCI-082			UCI-092	Yes
18	UCI-083	Yes						UCI-44			UCI-73	Yes
19	UCI-084	Yes						UCI-69			UI 49	Yes
20	UCI-085	No	29-Nov-17	TRUE	FALSE			UCI-75			UI-25	Yes
21	UCI-091	No	29-Nov-17	TRUE	FALSE			UI 53			UI-27	Yes
22	UI 51	No	2-Jan-17	TRUE	FALSE			UI-1			UI-31	Yes
23	UI 55	No	28-Oct-16	TRUE	FALSE			UI-20			UI-32	Yes
24	UI 60	No	14-Mar-17	TRUE	FALSE			UI-22			UI-40	Yes
25	UI-2	No	14-Nov-19	TRUE	FALSE			UI-23			UI-45	Yes
26	UI-22	No	14-Apr-21	TRUE	FALSE			UI-33			UI-5	Yes
27	UI-25	Yes						UI-38			UI-50	Yes
28	UI-27	Yes						UI-41			UI-57	Yes
29	UI-30	No	6-Jun-16	TRUE	FALSE			UI-43			UI-58	Yes
30	UI-33	No	26-May-16	TRUE	FALSE			UI-44			UI-62	Yes
31	UI-39	No	15-Mar-21	TRUE	FALSE			UI-52			UI-65	Yes
32	UI-41	No	3-Sep-20	TRUE	FALSE			UI-56			UI-7	Yes
33	UI-42	No	12-Jul-19	TRUE	FALSE			UI-61			UI-92	Yes
34	UI-43	No	8-Jun-18	TRUE	FALSE			UI-7
35	UI-45	Yes						UI-78
36	UI-47	No	28-Mar-17	TRUE	FALSE
37	UI-5	Yes
38	UI-51	No	29-Nov-17	TRUE	FALSE
39	UI-54	No	22-Mar-17	TRUE	FALSE
40	UI-55	No	29-Nov-17	TRUE	FALSE
41	UI-57	Yes
42	UI-59	No	9-Oct-19	TRUE	FALSE
43	UI-61	No	7-Jul-20	TRUE	FALSE
44	UI-62	Yes
45	UI-68	No	19-Nov-21	FALSE	TRUE
Stamp Availability Calc

Cell Formulas
Range	Formula
A2:A45	A2	=SORT(UNIQUE(FILTER(Table10[STAMP NO.],Table10[STATUS]="RETURNED")))
B2:B45	B2	=IF(A2<>"",IFERROR(INDEX(L:L,MATCH(A2,K:K,FALSE)),"No"),"")
C2:C45	C2	=IF(B2="No",XLOOKUP(MAX(Table10[DATE OF RETURN/LOST]),(Table10[STAMP NO.]='Stamp Availability Calc'!A2)*Table10[DATE OF RETURN/LOST],Table10[DATE OF RETURN/LOST],,-1),"")
D2:D45	D2	=IFERROR(DATEDIF(C2,TODAY(),"m")>=6, C2)
E2:E45	E2	=IFERROR(DATEDIF(C2,TODAY(),"m")<6, C2)
F2:F8	F2	=SORT(UNIQUE(FILTER(Table10[STAMP NO.],(Table10[STATUS]="BROKEN")*(Table10[DATE OF RETURN/LOST]>0))))
H2:H35	H2	=SORT(UNIQUE(FILTER(Table10[STAMP NO.],(Table10[STATUS]="LOST"))))
K2:K33	K2	=UNIQUE(FILTER(Table10[STAMP NO.],Table10[DATE OF RETURN/LOST]=""))
G2:G8	G2	=XLOOKUP(MAX(Table10[DATE OF RETURN/LOST]),(Table10[STAMP NO.]='Stamp Availability Calc'!F2)*Table10[DATE OF RETURN/LOST],Table10[DATE OF RETURN/LOST],,-1)
L2:L45	L2	=IF(K2<>"","Yes","")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F:G	Cell	does not contain a blank value	text	NO
E:E	Cell	does not contain a blank value	text	NO
D:D	Cell	does not contain a blank value	text	NO

 

[Fluff]

You are getting the circular reference because the formula in B2 needs to look at C2, but the formula in C2 is looking at B2

 

[willow1985]

You are getting the circular reference because the formula in B2 needs to look at C2, but the formula in C2 is looking at B2

Thank you very much! I have fixed this and the formulas work great.

Thank you again

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[willow1985]

I do have another question regarding another modification to a formula on that sheet but I will make a new post. Thank you again

 

[Fluff]

Ok,