Excel formula to match multiple criteria

I need an excel formula that will look at Location, Department Code and Job Title then return the Full Name. The problem that I am having is that locations can use the same department code and job title. There may also be multiple result which I will need to be shown in multiple cells. This will be used for an employee breakdown by location and department. I am using Excel 2016.

Example: I want to show all names that match the criteria below.

Location – Virginia

Department Code – 02023, 02023C, 02023A

Job Title – Porter, Porte/Serv, Detail, Service Porter, Detailer-Recon

This is the formula I use for positions that only have one criteria.
{=IFERROR(INDEX(Sheet1!$C$2:$C$800,SMALL(IF(Sheet1!$B$2:$B$800=”02021A”,ROW(Sheet1!$B$2:$B$800)-ROW(Sheet1!$B$2)+1),ROWS(EMPLOYEE!$B16:$B$16))),””)}

I think the easiest way to to use a macro. This one will work. Replace your sheet name with the one in your spreadsheet, place this in a module on in the VBE for the sheet.

Make sure to replace the 5 in this bit with the column where you want to print the name.

You can then filter on that column, and you have your list.

Let me know if this works.

Thanks!

ccccThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 5).Value = strCurrName

Sub find()

Dim strLocation As String
Dim strHOmeDept As String
Dim strJobTitle As String
Dim strCurrentLoc As String
Dim strCurrHomeDept As String
Dim strCurrJobTitle As String
Dim strCurrName As String

Dim intCurrRow As Integer
Dim intEndRow As Integer



Dim i As Integer

intCurrentRow = 1
intEndRow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row


For i = 1 To intEndRow

intCurrentRow = intCurrentRow + 1

strCurrentLoc = ThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 1).Value
strCurrHomeDept = ThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 2).Value
strCurrJobTitle = ThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 4).Value
strCurrName = ThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 3).Value

If strCurrentLoc = "VIRGINIA" And _
(strCurrHomeDept = "02023" Or strCurrHomeDept = "02023C" Or strCurrHomeDept = "02023A") _
And (strCurrJobTitle = "DETAILER-RECON" Or strCurrJobTitle = "PORTER" Or strCurrJobTitle = "DETAIL" Or strCurrJobTitle = "SERVICE PORTER" _
     Or strCurrJobTitle = "PORTE/SERV") Then

ThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 5).Value = strCurrName



End If

Next


End Sub

Your issue can be solved by using Advance Filter also:

How it works:

• Copy Headers in Row F70:I70.
• Enter Criteria in F71, G71:G73 & I71:I74.
• Click Advance Filter from Data Tab, Sort
  & Filter Menu.
• Fill cell references as shown in the Advance
  Filter dialogue below.

• Finish with Ok

N.B.

• Results may vary as Critetia been used.
• Adjust cell references as your need.