Index matching multiple criteria

[pedexe90]

So I have come across a pretty tricky situation.
Here is the question:


This is part of a much larger issue but I have taken out a sample. I have two worksheets as you can see attached, one is for auditing and the other is where I input raw data. What I need to do is to match date and the title (Sterling Eer and Sterling SE Eer) as well as the names to index data from the raw data page (Payroll_FT) and to populate all cells.


Now my formula worked before when I was only matching dates and names but when I added another row of data containing Sterling Eer and Sterling SE, I cannot match the data accordingly.


=INDEX(PAYROLL_FT!$B$1:$BR$1001,MATCH($A7,INDEX(PAYROLL_FT!$B$1:$BR$1001,,MATCH(B$1&B$5,PAYROLL_FT!$B$1:$BR$1&PAYROLL_FT!$B$2:$BR$2,0)-1),0),MATCH(B$1&B$5,PAYROLL_FT!$B$1:$BR$1&PAYROLL_FT!$B$2:$BR$2,0))


Is there anyway to modify the formula above to do this? I have uploaded the file on OneDrive as it is a bit difficult to explain.


Many thanks in advance,

https://1drv.ms/x/s!AgQdYsSdg735gQXiWRUxFC45_BlH

 

[Fluff]

Try
=INDEX(PAYROLL_FT!$B$1:$BR$1001,MATCH($A6,PAYROLL_FT!$B$1:$B$1001,0),MATCH(B$1&B$5,PAYROLL_FT!$B$1:$BR$1&PAYROLL_FT!$B$2:$BR$2,0))

Entered with Ctrl Shift Enter, not just enter.
Then copy across & down

 

[pedexe90]

Thanks for the reply.

But isn't this only reading the names on column PAYROLL_FT!$B$1:$B$1001?

This should change to the new column every month as the names would vary (new names added or removed).

 

[pedexe90]

Try
=INDEX(PAYROLL_FT!$B$1:$BR$1001,MATCH($A6,PAYROLL_FT!$B$1:$B$1001,0),MATCH(B$1&B$5,PAYROLL_FT!$B$1:$BR$1&PAYROLL_FT!$B$2:$BR$2,0))

Entered with Ctrl Shift Enter, not just enter.
Then copy across & down

So as you can see on my raw data page, I have created a stack up column which stacks up all the columns, and on the audit tab, I am using an index count to only return unique names. This allows me to create a dynamic column of data which means if new people are added or removed this will add them to the bottom. That is why in my initial function I had an index match within an index match to find the right column and read the names from that column (based on the month).

 

[Fluff]

What's wrong with the formula you are using?