Looking for some assistance here as I rack my brain for a solution.

I have a Scorecard workbook that upon selection of the employee name and month - I want the statistics to update per employee and month.

The data I am referencing is in a separate Audit workbook and is listed by employees in Column A, but monthly data is broken out per month in multiple columns (i.e B, E, H, K, ETC.)

What I am trying to index is the data in columns B, E, H, etc. depending on the employee name in the Scorecard matching the employee name in column A of the Audit log AND the month in the Scorecard matching the month in Row 1 in the Audit log. So formula speaking it's something like this

Formula: copy to clipboard 


INDEX('Audit'!B:B,AND(MATCH(Scorecard!C3,'Audit'!A:A,0),(MATCH(Scorecard!I3,'Audit'!1:1,0))


For the sake of argument here, pretend Audit and Scorecard are separate files NOT worksheets like how i have written this.

Except where B is I need this to be the multiple columns I mentioned, so that whatever month and employee is I can return the correct result (i.e. Employee = Sally, Month = January, in the Audit log, Sally's January results would be found in cell B2)

Index is just what I am semi familiar with so I tried it. I also tried doing a PivotTable using the data in the Audit log but it was not calculating the true data from the source.

Help please! Open to suggestions/tips