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

 

[willow1985]

Glad to help & thanks for the feedback.

I received a reply from a SQL Forum and they stated that it was my formula in excel that was the problem and not the SQL code.

He stated the following:

"The problem doesn't appear to be the data - the problem is in the formula. That formula is expecting the year to be in a non-text format and fails because the column is defined as text.

If you are just refreshing the data in Excel - then the column data type shouldn't change. But - it sounds like you are not actually modifying the columns data type.

This doesn't appear to be an issue on the SQL side - rather a problem with the column definition in Excel and/or the formula. I would try fixing the formula so it converts the value from that column to a number - which can be done using VALUE or adding 0 to the column."

How would I fix the formula so it converts the value to a number / use value in this formula to avoid the error?

 

[willow1985]

I figured it out. The below formula now works:

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

 

[Fluff]

Personally I would disagree with whoever said it was the formula. Numbers should be imported as numbers & not as text, that is a basic rule of Excel.

 

[willow1985]

I am very confused. He is saying it is an excel table problem and no matter how I try and format or "cast" that line of SQL code the result is the same. I am just too new to SQL I think.

 

[Alex Blakenburg]

If you are refreshing the query to an existing table, you can try this.

• Make sure you don't have any filters on your table.
• Hover over the heading of the Year field until you get a very dark downward pointing arrow and then click to select.
(or click in any cell in the Year field and hit Ctrl+Spacebar)
• Change the format to "General"

If you now refresh the query is Year being recognised as a number ?