# Index match formula with multiple matches



## willow1985 (Thursday at 11:48 AM)

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.xlsmL1#N/AWO_Reprocess_DataCell FormulasRangeFormulaL1L1=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.xlsmL1#N/AWO_Reprocess_DataCell FormulasRangeFormulaL1L1=INDEX(F:F,MATCH(1,(B:B=Analysis!P2)*(A:A=Analysis!P1),0))Named RangesNameRefers ToCellsQuery_from_Uni_Inc__WP=WO_Reprocess_Data!$A$1:$C$39L1

Reprocesses Additional Data WP.xlsmABCDEFGHI1Shipped MonthShipped YearCount of Work Orders ShippedCountCount over WO Shipped% that were Operator DefectsCostCount of All Reprocess and AdditionalsWO Interruptions2Dec20192126124%31%$086410%3Jan2020590244%21%$07312%4Feb2020410236%4%$06716%5Mar2020407246%25%$06917%6Apr2020358319%35%$07220%7May2020282166%31%$04717%8Jun2020235209%15%$05925%9Jul2020266166%38%$05220%10Aug2020257177%35%$05321%11Sep2020279218%38%$06122%12Oct2020320268%35%$05317%13Nov2020317237%48%$05618%14Dec20202422310%48%$04719%15Jan20212723814%39%$07327%16Feb2021284186%56%$05118%17Mar20213593911%44%$06318%18Apr2021270249%21%$04617%19May20212432410%33%$03916%20Jun2021430276%59%$05312%21Jul20212962910%45%$04917%22Aug2021245187%44%$04217%23Sep2021304186%61%$04314%24Oct20212492912%31%$04518%25Nov2021286279%30%$05419%26Dec2021261177%24%$03413%27Jan2022598224%59%$28,135386%28Feb2022304155%40%$21,8504214%29Mar2022328268%31%$31,0404915%30Apr2022718233%17%$23,430527%31May2022356278%52%$36,9065215%32Jun2022369288%43%$33,7286417%33Jul20223002910%55%$31,3458829%34Aug2022409297%34%$31,2656616%35Sep2022431317%23%$46,5606515%36Oct20222713312%33%$54,6955320%37Nov2022393297%41%$43,2305915%38Dec20223123311%21%$49,7404715%39Jan20231041817%50%$25,4553837%WO_Reprocess_DataCell FormulasRangeFormulaD2:D39D2=COUNTIFS(WP_RA[Rework Year],[@[Shipped Year]],WP_RA[Rework Month Name],[@[Shipped Month]],WP_RA[Additional/Reprocess],DATA!$A$1)E2:E39E2=[@Count]/[@[Count of Work Orders Shipped]]F2:F39F2=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:G39G2=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:H39H2=COUNTIFS(WP_RA[Rework Year],[@[Shipped Year]],WP_RA[Rework Month Name],[@[Shipped Month]])I2:I39I2=[@[Count of All Reprocess and Additionals]]/[@[Count of Work Orders Shipped]]

Reprocesses Additional Data WP.xlsmOP1Month Presenting:Dec2Year Presenting:2022AnalysisCells with Data ValidationCellAllowCriteriaP1List=Categories!$D$1:$D$12P2List=Categories!$K$1:$K$8


----------



## Fluff (Thursday at 11:57 AM)

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


----------



## willow1985 (Thursday at 12:55 PM)

Fluff said:


> 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 (Thursday at 1:03 PM)

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 (Thursday at 1:46 PM)

Fluff said:


> 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 (Thursday at 1:51 PM)

willow1985 said:


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


Yes please.


----------



## willow1985 (Thursday at 1:54 PM)

Fluff said:


> Yes please.


Which forum for SQL questions?


----------



## Fluff (Thursday at 2:06 PM)

Probably best in the General Discussion section.


----------



## willow1985 (Thursday at 2:30 PM)

Fluff said:


> 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 (Thursday at 2:36 PM)

Glad to help & thanks for the feedback.


----------



## willow1985 (Thursday at 11:48 AM)

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.xlsmL1#N/AWO_Reprocess_DataCell FormulasRangeFormulaL1L1=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.xlsmL1#N/AWO_Reprocess_DataCell FormulasRangeFormulaL1L1=INDEX(F:F,MATCH(1,(B:B=Analysis!P2)*(A:A=Analysis!P1),0))Named RangesNameRefers ToCellsQuery_from_Uni_Inc__WP=WO_Reprocess_Data!$A$1:$C$39L1

Reprocesses Additional Data WP.xlsmABCDEFGHI1Shipped MonthShipped YearCount of Work Orders ShippedCountCount over WO Shipped% that were Operator DefectsCostCount of All Reprocess and AdditionalsWO Interruptions2Dec20192126124%31%$086410%3Jan2020590244%21%$07312%4Feb2020410236%4%$06716%5Mar2020407246%25%$06917%6Apr2020358319%35%$07220%7May2020282166%31%$04717%8Jun2020235209%15%$05925%9Jul2020266166%38%$05220%10Aug2020257177%35%$05321%11Sep2020279218%38%$06122%12Oct2020320268%35%$05317%13Nov2020317237%48%$05618%14Dec20202422310%48%$04719%15Jan20212723814%39%$07327%16Feb2021284186%56%$05118%17Mar20213593911%44%$06318%18Apr2021270249%21%$04617%19May20212432410%33%$03916%20Jun2021430276%59%$05312%21Jul20212962910%45%$04917%22Aug2021245187%44%$04217%23Sep2021304186%61%$04314%24Oct20212492912%31%$04518%25Nov2021286279%30%$05419%26Dec2021261177%24%$03413%27Jan2022598224%59%$28,135386%28Feb2022304155%40%$21,8504214%29Mar2022328268%31%$31,0404915%30Apr2022718233%17%$23,430527%31May2022356278%52%$36,9065215%32Jun2022369288%43%$33,7286417%33Jul20223002910%55%$31,3458829%34Aug2022409297%34%$31,2656616%35Sep2022431317%23%$46,5606515%36Oct20222713312%33%$54,6955320%37Nov2022393297%41%$43,2305915%38Dec20223123311%21%$49,7404715%39Jan20231041817%50%$25,4553837%WO_Reprocess_DataCell FormulasRangeFormulaD2:D39D2=COUNTIFS(WP_RA[Rework Year],[@[Shipped Year]],WP_RA[Rework Month Name],[@[Shipped Month]],WP_RA[Additional/Reprocess],DATA!$A$1)E2:E39E2=[@Count]/[@[Count of Work Orders Shipped]]F2:F39F2=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:G39G2=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:H39H2=COUNTIFS(WP_RA[Rework Year],[@[Shipped Year]],WP_RA[Rework Month Name],[@[Shipped Month]])I2:I39I2=[@[Count of All Reprocess and Additionals]]/[@[Count of Work Orders Shipped]]

Reprocesses Additional Data WP.xlsmOP1Month Presenting:Dec2Year Presenting:2022AnalysisCells with Data ValidationCellAllowCriteriaP1List=Categories!$D$1:$D$12P2List=Categories!$K$1:$K$8


----------



## willow1985 (Yesterday at 3:45 PM)

Fluff said:


> 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 (Yesterday at 3:49 PM)

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 (Yesterday at 3:58 PM)

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 (Yesterday at 8:15 PM)

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.


----------

