Excel 2016公式#NAME?报错求助：计算宝可梦最优队伍属性

Why the #NAME? Error Occurs #

Your formula is throwing this error for one or more of these reasons:

• Invalid structured references: [Type] only works if the formula is inside an Excel Table with a Type column. If you're using a regular cell range, replace [Type] with a specific cell reference (e.g., $A$1).
• Unrecognized table/column names: Double-check that Dex_Info and Type_Counters are valid Excel Tables (verify via Formulas > Name Manager) and that columns like Type, Type 1, and the Normal:Fairy range exist exactly as spelled.
• Syntax issues: Ensure all table references are properly formatted (no missing brackets or typos).

Fixed Formula Example #

Assuming your attacking type is in cell A1, here’s the corrected formula to calculate total damage multiplier for that type against all enemy Pokémon:

=SUMPRODUCT(INDEX(Type_Counters[[Normal]:[Fairy]], MATCH(Dex_Info[Type 1], Type_Counters[Type], 0), MATCH($A$1, Type_Counters[Type], 0)) + INDEX(Type_Counters[[Normal]:[Fairy]], MATCH(Dex_Info[Type 2], Type_Counters[Type], 0), MATCH($A$1, Type_Counters[Type], 0)))

This sums the effectiveness of the attacking type against both Type 1 and Type 2 of every enemy Pokémon.

Optimized Workflow for Finding the Best Type #

1. List all possible types in a range (e.g., A1:A18 for the 18 core Pokémon types).
2. Calculate total effectiveness for each type:
   • In cell B1, enter the formula above, replacing $A$1 with A1.
   • Drag the formula down to B18 to compute scores for all types.
3. Find the optimal type:
   Use this formula to get the type with the highest total damage score:

   =INDEX(A1:A18, MATCH(MAX(B1:B18), B1:B18, 0))
   

Alternative Formula (VLOOKUP for Readability) #

If you prefer VLOOKUP over INDEX/MATCH, use this version (handles missing custom types with a neutral 1x multiplier):

=SUMPRODUCT(IFERROR(VLOOKUP(Dex_Info[Type 1], Type_Counters, MATCH($A$1, Type_Counters[#Headers], 0), FALSE), 1) + IFERROR(VLOOKUP(Dex_Info[Type 2], Type_Counters, MATCH($A$1, Type_Counters[#Headers], 0), FALSE), 1))

内容的提问来源于stack exchange，提问作者Laurens Wolf