CODEWITHSUNDEEP
exceldatedatetimeexcel-formulaexcel-2016
M Spink
M Spink

Reputation: 13

MATCH, INDEX and SMALL Formula for Dates and Times with Multiple Criteria and Multiple Values

I have tried everything but cannot sort this one out. I have multiple sheets in the same book. One with just a list of times, dates and employee numbers. The other sheets are my employees. Each sheet has the employee's name and other details that has to be sorted from my first sheet.

The first sheet looks like this:

enter image description here

The second sheet has to sort all data from the first sheet according to employee number and date. What I would like to do is get the time from my first sheet based on my date and employee number into my second sheet. Here is the problem . . . the IF function does not want to find any value based on a date criteria. I have tried countless ways to work around it. My first sheet does not contain just the date and time but the date and time column both have the date and the time together. i have tried splitting it but no success on my formula.

The only formula I found to work was 

=IFERROR(INDEX(ALL!$C:$C,MATCH('JOHAN HORAK'!C14&$E$2,ALL!$H:$H&ALL!$A:$A,0)),0)

this pulls the first value but I need the second, third and fourth value to appear as well but an INDEX, SMALL, IF function only returns and error. My second sheet will have to have the clock in and out times in their appropriate columns.

enter image description here

Upvotes: 1

Views: 1123

Answers (1)

Jacob Edmond
Jacob Edmond

Reputation: 808

Try this:

=IFERROR(INDEX(ALL!$C:$C,AGGREGATE(15,6,ROW(ALL!$H:$H)/((ALL!$H:$H='JOHAN HORAK'!C14)*(ALL!$A:$A=E2)),1),0),0)

Upvotes: 0

Related Questions