Index match formula to search for two criteria (date & name)

[davechamp1983]

Good Evening

I require an Index match formula to be able to search for two criteria 1. someones Name 2. The Date

I have a master data file where the different bits of information are seperated out in different tabs but have pretty much the same format in each one, I have attached the workbooks I am using so you can see what I am trying to achieve.

But for example

I want a formula in Cell C31 of the team daily stats workbook to look for Agent Chris ollerhead and the date 02/01/13 in the Nuber of calls tab in the agent master data workbook and return the answer 24 and then for the 03/01/13 the answer should be 35.

I want to do this for the Not Ready tab, the Log In tab & the Log out tab, the other fields get entered manually.

Here is the code I entered but it doesnt seem to work

=INDEX(INDEX('[Agent Master Data.xlsm]Number Of Calls'!$A$3:$ZZ$220,0,MATCH(C$1,'[Agent Master Data.xlsm]Number Of Calls'!$1:$1,0)+1),MATCH($A$30,INDEX('[Agent Master Data.xlsm]Number Of Calls'!$A$3:$ZZ$220,0,MATCH(C$1,'[Agent Master Data.xlsm]Number Of Calls'!$1:$1,0)),0))

any assistance you can provide would be much appreciated as I just cant see what I am doing wrong here

[rylo]

Hi

I think you have your index parameters reversed. You have the selection to find the column first, then the row.

Without all the trickery this seemed to work for me for Amy's Team sheet Jan 13 C31:

=INDEX('[Agent Master Data.xlsm]Number Of Calls'!1:79,MATCH(A30,'[Agent Master Data.xlsm]Number Of Calls'!$A:$A,0),MATCH(C1,'[Agent Master Data.xlsm]Number Of Calls'!1:1,0))

HTH

rylo

[dredwolf]

Mine turns out to be very similar to rylo, just some referencing differences really :
In B31:

=IFERROR(INDEX('[Agent Master Data.xlsm]Number Of Calls'!$A$3:$ZZ$66,MATCH($A30,'[Agent Master Data.xlsm]Number Of Calls'!$A$3:$A$66,0),MATCH(C$1,'[Agent Master Data.xlsm]Number Of Calls'!$A$1:$ZZ$1,0)),"")

Drag across

Hope this helps

[vlady]

in case there is a change in the names positions maybe maybe also like this

=VLOOKUP(A30,INDIRECT(CELL("address",INDEX('[Agent Master Data.xlsm]Number Of Calls'!$A$1:$D$66,0,MATCH(C$1,'[Agent Master Data.xlsm]Number Of Calls'!1:1,0)-1))&":$D$66"),2,FALSE)