Help with Xlookup with a SUMIFS XLOOKUP, INDEX, and MATCH

[Jonny424]

Hi there! I am reaching out to you to see if someone can help me with this excel problem.

Essentially I want it to look up two criteria, say, Depart # and Type of Pay (like Gross Pay); I want it to match each one and sum up all the amounts that match in that column to the department giving me a grand total for all Gross Pays for Department #.

There are two sets of data tables.

The formula will live here in Column F starting on F7 onward:
JE Totals.xlsx

And all the data live in this spreadsheet with the same names in Column C from JE Totals.
Master Data Table.xlsx

I've tried a couple of things like a pivot table, but there is 20 items total per department, and then the end user wants the grand totals at the bottom. That's why I thought a SUMIFS XLOOKUP, INDEX, And MATCH would be the best way but am not an expert at Excel to make it work perfectly. That is why I am asking for help.

 

[Leo Skywalker]

In F7:

=SUMIFS(INDEX('Master Data Table.xlsx'!Table13[#Data],0,MATCH(C7,'Master Data Table.xlsx'!Table13[#Headers],0)),'Master Data Table.xlsx'!Table13[Worked In Department],A7)

W- Capital Campaign
EE Cont to Med/Dental

Will return

#N/A

, as they don't exist in the table's headers. Maybe you can replace
W- Capital Campaign with W_Capital Campaig_total_deductions in column AY of the Master Table.

 

[Jonny424]

In F7:

=SUMIFS(INDEX('Master Data Table.xlsx'!Table13[#Data],0,MATCH(C7,'Master Data Table.xlsx'!Table13[#Headers],0)),'Master Data Table.xlsx'!Table13[Worked In Department],A7)

W- Capital Campaign
EE Cont to Med/Dental

Will return

#N/A

, as they don't exist in the table's headers. Maybe you can replace
W- Capital Campaign with W_Capital Campaig_total_deductions in column AY of the Master Table.

You are a God Send! THANK YOU!!!!

 

[Leo Skywalker]

You're welcome!!