Index match formula with multiple matches

[willow1985]

I have entered in the below index match formula multiple ways but I am still receiving a #N/A error when I know the result should be 21%
If anyone can point out what I am doing wrong it would be greatly appreciated!

Thank you!

First Formula Attempt:

Reprocesses Additional Data WP.xlsm
	L
1	#N/A
WO_Reprocess_Data

Cell Formulas
Range	Formula
L1	L1	=INDEX(WO_Reprocess_Data[% that were Operator Defects],MATCH(1,(WO_Reprocess_Data[Shipped Year]=Analysis!P2)*(WO_Reprocess_Data[Shipped Month]=Analysis!P1),0))

Second Formula Attempt:

Reprocesses Additional Data WP.xlsm
	L
1	#N/A
WO_Reprocess_Data

Cell Formulas
Range	Formula
L1	L1	=INDEX(F:F,MATCH(1,(B:B=Analysis!P2)*(A:A=Analysis!P1),0))

Named Ranges
Name	Refers To	Cells
Query_from_Uni_Inc__WP	=WO_Reprocess_Data!$A$1:$C$39	L1

Reprocesses Additional Data WP.xlsm
	A	B	C	D	E	F	G	H	I
1	Shipped Month	Shipped Year	Count of Work Orders Shipped	Count	Count over WO Shipped	% that were Operator Defects	Cost	Count of All Reprocess and Additionals	WO Interruptions
2	Dec	2019	21	26	124%	31%	$0	86	410%
3	Jan	2020	590	24	4%	21%	$0	73	12%
4	Feb	2020	410	23	6%	4%	$0	67	16%
5	Mar	2020	407	24	6%	25%	$0	69	17%
6	Apr	2020	358	31	9%	35%	$0	72	20%
7	May	2020	282	16	6%	31%	$0	47	17%
8	Jun	2020	235	20	9%	15%	$0	59	25%
9	Jul	2020	266	16	6%	38%	$0	52	20%
10	Aug	2020	257	17	7%	35%	$0	53	21%
11	Sep	2020	279	21	8%	38%	$0	61	22%
12	Oct	2020	320	26	8%	35%	$0	53	17%
13	Nov	2020	317	23	7%	48%	$0	56	18%
14	Dec	2020	242	23	10%	48%	$0	47	19%
15	Jan	2021	272	38	14%	39%	$0	73	27%
16	Feb	2021	284	18	6%	56%	$0	51	18%
17	Mar	2021	359	39	11%	44%	$0	63	18%
18	Apr	2021	270	24	9%	21%	$0	46	17%
19	May	2021	243	24	10%	33%	$0	39	16%
20	Jun	2021	430	27	6%	59%	$0	53	12%
21	Jul	2021	296	29	10%	45%	$0	49	17%
22	Aug	2021	245	18	7%	44%	$0	42	17%
23	Sep	2021	304	18	6%	61%	$0	43	14%
24	Oct	2021	249	29	12%	31%	$0	45	18%
25	Nov	2021	286	27	9%	30%	$0	54	19%
26	Dec	2021	261	17	7%	24%	$0	34	13%
27	Jan	2022	598	22	4%	59%	$28,135	38	6%
28	Feb	2022	304	15	5%	40%	$21,850	42	14%
29	Mar	2022	328	26	8%	31%	$31,040	49	15%
30	Apr	2022	718	23	3%	17%	$23,430	52	7%
31	May	2022	356	27	8%	52%	$36,906	52	15%
32	Jun	2022	369	28	8%	43%	$33,728	64	17%
33	Jul	2022	300	29	10%	55%	$31,345	88	29%
34	Aug	2022	409	29	7%	34%	$31,265	66	16%
35	Sep	2022	431	31	7%	23%	$46,560	65	15%
36	Oct	2022	271	33	12%	33%	$54,695	53	20%
37	Nov	2022	393	29	7%	41%	$43,230	59	15%
38	Dec	2022	312	33	11%	21%	$49,740	47	15%
39	Jan	2023	104	18	17%	50%	$25,455	38	37%
WO_Reprocess_Data

Cell Formulas
Range	Formula
D2:D39	D2	=COUNTIFS(WP_RA[Rework Year],[@[Shipped Year]],WP_RA[Rework Month Name],[@[Shipped Month]],WP_RA[Additional/Reprocess],DATA!$A$1)
E2:E39	E2	=[@Count]/[@[Count of Work Orders Shipped]]
F2:F39	F2	=COUNTIFS(WP_RA[Rework Year],[@[Shipped Year]],WP_RA[Rework Month Name],[@[Shipped Month]],WP_RA[Additional/Reprocess],DATA!$A$1,WP_RA[Operator/Process],"Operator")/[@Count]
G2:G39	G2	=SUMIFS(WP_RA[Cost],WP_RA[Rework Month Name],[@[Shipped Month]],WP_RA[Rework Year],[@[Shipped Year]],WP_RA[Additional/Reprocess],DATA!$A$1)
H2:H39	H2	=COUNTIFS(WP_RA[Rework Year],[@[Shipped Year]],WP_RA[Rework Month Name],[@[Shipped Month]])
I2:I39	I2	=[@[Count of All Reprocess and Additionals]]/[@[Count of Work Orders Shipped]]

Reprocesses Additional Data WP.xlsm
	O	P
1	Month Presenting:	Dec
2	Year Presenting:	2022
Analysis

Cells with Data Validation
Cell	Allow	Criteria
P1	List	=Categories!$D$1:$D$12
P2	List	=Categories!$K$1:$K$8

 

[Fluff]

Make sure that the years are real numbers & not numbers stored as text.

 

[willow1985]

Make sure that the years are real numbers & not numbers stored as text.

Formatting does not seem to make a difference. It was originally formatted as General but switching to number did not make a difference and I get the same error

 

[Fluff]

Changing the format does not change the actual value in the cells.
Try formatting the entire column as a date. Do you see a date or just the year?

 

[willow1985]

Changing the format does not change the actual value in the cells.
Try formatting the entire column as a date. Do you see a date or just the year?

I see the Year. I found that doing a text to column set to General works but I have no idea why it is not coming through this format already with SQL. When I check the format is shows as General but I suppose it isnt and I have no idea how to modify the SQL code to correct this.

I suppose this turned into a SQL question. Should I re-post?

 

[Fluff]

I suppose this turned into a SQL question. Should I re-post?

Yes please.

 

[willow1985]

Yes please.

Which forum for SQL questions?

 

[Fluff]

Probably best in the General Discussion section.

 

[willow1985]

Probably best in the General Discussion section.

Done.

SQL Cast from unixtime Year in general format that Excel can recognize

I hope I have the right forum for this question... I am very new to SQL and have this command text where this line is bringing in the year of shipped data. However Excel does not recognize the data. I can fix this by doing a text to column and selecting General but I do not want to do this...

www.mrexcel.com

Thank you

 

[Fluff]

Glad to help & thanks for the feedback.