Excel formula to match multiple criteria

Posted on


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.


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.


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


End Sub

Your issue can be solved by using Advance Filter also:

enter image description here

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
  • Fill cell references as shown in the Advance
    Filter dialogue below.

enter image description here

  • Finish with Ok


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

Leave a Reply

Your email address will not be published. Required fields are marked *