# Showing most recent date for multiple employees

#### errollflynn

##### New Member
Hello everyone!

I have an issue I've been banging my head against the wall trying to solve. I have a spreadsheet broken down as follows:

Column A: A dropdown list with multiple recruiters names.
Column B: Manually entered date when a recruiter conducted an interview. A recruiter can only do one interview a day.
Column Q, i.e. the column I'm trying to create: Contains the most recent date a recruiter conducted an interview.
Additional columns that do not have any relevance to what I'm trying to accomplish.

I would like to have a column that would show the most recent date a recruiter conducted an interview. For example, recruiter A may have conducted interviews on 1/1/2021, 1/5/2021, and 1/19/2021. I need to create an expression that will first identify the recruiter's name in column A, then evaluate the dates associated with that recruiter's name in column B, and then return the most recent value in column Q.

This is my first time posting on the boards so please forgive me if I did not approach posing the question correctly.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Eric W

##### MrExcel MVP
Welcome to the MrExcel forum!

Try:

Excel Formula:
``=MAXIFS(B:B,A:A,A2)``

#### errollflynn

##### New Member
Hello Eric and thank you for your reply. This is a start but I've run into an issue with the criteria being in the same column. I'm not sure how to write the expression with the recruiters/criteria in the same column. The examples I have seen have multiple criteria in different columns.

#### Eric W

##### MrExcel MVP
What criteria are you referring to? Here's my sample sheet:

Book1
ABQ
1RecruiterDateMost recent date
2A1/1/20213/1/2021
3A1/2/20213/1/2021
4B2/2/20212/2/2021
5C2/3/20212/3/2021
6A1/5/20213/1/2021
7A3/1/20213/1/2021
8B1/4/20212/2/2021
9E2/2/20212/2/2021
10F
11E2/2/2021
12
Sheet9
Cell Formulas
RangeFormula
Q2:Q12Q2=IF(MAXIFS(B:B,A:A,A2)=0,"",MAXIFS(B:B,A:A,A2))

Does this match your sheet? You can use the XL2BB (Excel to Bulletin Board) tool in the response box to show a sample of your sheet, and explain what your data looks like, and what the desired results are. It's easy to set up and use.

Last edited:

#### errollflynn

##### New Member

Below is what I have when I try to use multiple criteria. I see your expression and it works the way I would like mine to. I do not fully understand the arguments as yours is written. For example, yours begins with a standard "=if" and has the "maxifs" nested inside the expression. Also, you have the expression set to 0 and have a "" with no values inside. If you could explain the expression that would be a great help. Thank you for your time on this.

Test Book.xlsx
ABCDE
1Interview DateRecruiterRecruiterDate Last Used
21/4/2021Khari Shiver1/0/00
31/1/2021Christine Park1/0/00
41/5/2021Alicia Quinonez1/0/00
51/5/2021Fartin Martin1/0/00
61/6/2021Alicia Quinonez1/0/00
71/3/2021Christine Park1/0/00
81/10/2021Fartin Martin1/0/00
91/3/2021Alicia Quinonez1/0/00
101/14/2021Alicia Quinonez1/0/00
111/7/2021Khari Shiver1/0/00
Sheet1
Cell Formulas
RangeFormula
E2:E11E2=MAXIFS(A2:A11,B2:B11,"Khari Shiver",B2:B11,"Christine Park",B2:B11,"Alicia Quinonez",B2:B11,"Fartin Martin")
Cells with Data Validation
CellAllowCriteria
D2:D11List='Validation Data'!\$A\$2:\$A\$5
B2:B11List='Validation Data'!\$A\$2:\$A\$5

#### Eric W

##### MrExcel MVP
Given your layout, I'd suggest something like this:

Book1
ABCDE
1Interview DateRecruiterRecruiterDate Last Used
21/4/2021Khari ShiverChristine Park1/3/2021
31/1/2021Christine ParkFartin Martin1/10/2021
41/5/2021Alicia Quinonez
51/5/2021Fartin Martin
61/6/2021Alicia Quinonez
71/3/2021Christine Park
81/10/2021Fartin Martin
91/3/2021Alicia Quinonez
101/14/2021Alicia Quinonez
111/7/2021Khari Shiver
12
Sheet9
Cell Formulas
RangeFormula
E2:E11E2=IF(D2="","",MAXIFS(A:A,B:B,D2))

As far as the E2 formula goes, it first checks to see if D2 is empty (=""), and if so, just makes the result an empty cell (""). If there is an actual name in D2, it performs the MAXIFS. The A:A is the column with the dates in it. The B:B is the column with the recruiters' names in it, and D2 is the name you're looking for. So it looks down every cell in column B and finds "Christine Park" and keeps track of the corresponding date in column A. Once it finds them all, it takes the largest (most recent date), and displays that.

#### errollflynn

##### New Member

This is fantastic! Thank you so much for all your help.

#### Eric W

##### MrExcel MVP
Happy to help! Thanks for the feedback.

By the way, it looks like we both like old movies. My avatar is a picture of Harold Lloyd.

Indeed!

Replies
4
Views
170
Replies
4
Views
112
Replies
2
Views
61
Replies
3
Views
52
Replies
4
Views
48

1,127,466
Messages
5,624,908
Members
416,064
Latest member
PaulBr2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back