Search code examples
excelexcel-formulamatchexcel-2010sumifs

Issues with Excel SUMIF INDEX MATCH

I'm working on an Excel 2010 sheet using a series of tables to calculate a variable sum. See the below image for what I'm working with.

Encounter Calculations:

Table 1 Table_Encounters. The column XP Limit is supposed to look at the current value in Difficulty, match appropriate column in Table 2 Table_Players, then add together all numbers in that specific column.

For example, in G3, it should be referencing F3 (Hard), finding Table_Players[Hard], and adding together O3:O10, displaying a result of 8000 (1100+1100+1100+1100+1100+1400+1100).

Every combination of sumif, index, and match I try either returns 0, #ref, or #value. I have no idea what I'm doing wrong, and have been scratching my head over this for the last few hours. I thought I was going to the right direction with this, but I only get #REF so I'm at a loss:

=SUMIFS(INDEX([Difficulty],,MATCH(F$3,Table_Players[#Headers],0)),[Difficulty],$F4)
Solution

  • Use this in G3:

    =SUM(INDEX($K$3:$Q$10,0,MATCH($F3,$K$2:$Q$2,0)))

    Or with your structured references:

    =SUM(INDEX(Table_Players[#Data],0,MATCH($F3,Table_Players[#Headers],0)))