INDEXING ROWS for multiple criteria

[cmholmes]

Hello,

I'm trying to work through a formula for indexing rows based on 3 criteria. This is the what the data dumb looks like:

EmployeeID	FirstName	LastName	Location	JobTitle	DeptCode
1	john	smith	FL	Sales Associate	101
2	samantha	jones	FL	Sales Associate	101
3	james	clark	FL	Sales Manager	101
4	justine	baker	CA	President	105
5	sarah	miller	TX	Sales Associate	101

<tbody>
</tbody>

I am setting up a worksheet that has a set number of Job Titles for each location and department and I'm trying to set up a way to index those that are currently employed and leave a blank for any position that needs to be filled. So the Result would look like this:

EmployeeID	FirstName	LastName	Location	JobTitle	DeptCode
1	john	smith	FL	Sales Associate	101
2	samantna	jones	FL	Sales Associate	101
-	-	-	FL	Sales Associate	101
3	james	clark	FL	Sales Manager	101
4	justine	baker	CA	President	105
5	sarah	miller	TX	Sales Associate	101
-	-	-	TX	Sales Associate	101
-	-	-	TX	Sales Associate	101
-	-	-	TX	Sales Manager	101

<tbody>
</tbody>

The Locations, Job Titles and DeptCodes are set(hard coded) and the EmployeeID, First Name and LastName would be INDEXed from the data sheet.

I've tried using a IF-INDEX formula that references the columns as defined names but I couldn't get the formula to extend past one Locations, Job Titles and DeptCodes type.

 

[cmholmes]

So this formula is working pretty well but it is not capturing all of the data. This is the formula I have in the first cell of the EmployeeID Column.

"{=IF(COUNTIFS(D$10:D10,$D10,E$10:E10,$E10,F$10:F10,$F10)<=COUNTIFS('Data'!$D:$D,$D10,'Salary Raw'!$E:$E,$F10,'Data'!$F:$F,$E10),INDEX(INDIRECT(A$9),SMALL(IF(Location=$D10,IF(JobTitle=$E10,IF(DeptCode=$F10,ROW(EmployeeId)-ROW('Data'!$D$6)+1))),COUNTIFS(D$10:D10,$D10,E$10:E10,$E10,F$10:F10,$F10))),"-")}"

 

[cmholmes]

This works, I double checked.

So this formula is working pretty well but it is not capturing all of the data. This is the formula I have in the first cell of the EmployeeID Column.

"{=IF(COUNTIFS(D$10:D10,$D10,E$10:E10,$E10,F$10:F10,$F10)<=COUNTIFS('Data'!$D:$D,$D10,'Salary Raw'!$E:$E,$F10,'Data'!$F:$F,$E10),INDEX(INDIRECT(A$9),SMALL(IF(Location=$D10,IF(JobTitle=$E10,IF(DeptCode=$F10,ROW(EmployeeId)-ROW('Data'!$D$6)+1))),COUNTIFS(D$10:D10,$D10,E$10:E10,$E10,F$10:F10,$F10))),"-")}"