Return last date checked

[Jabe]

Hi all,

I have a sheet that tracks routine QA checks for some staff which looks a bit like this:

Name	Check Date	Score
Allan	04/01/24	5
Beverley	12/03/24	2
Charlie	16/04/24	4
Charlie	03/05/24	3
Allan	30/05/24	1
Charlie	15/06/24	4
Beverley	22/08/24	2

I have another sheet that I would like to return the last date each staff member had their work checked. It looks like this:

Name	Last Date Checked	Manual
Allan		30/05/24
Beverley		22/08/24
Charlie		15/06/24

I would like column B (Last date checked) to lookup the first table and return the most recent date the staff member in column A was checked - the correct answers are in the Manual column for context.

Can anyone suggest a formula that would work on this? This is a simplified version of my sheet as we have about 80 staff to check, so would prefer not to do it manually!

Thanks!

 

[hagia_sofia]

Hello, try:

=IFERROR(TAKE(SORT(FILTER(Sheet1!$B$2:$B$8,Sheet1!$A$2:$A$8=Sheet2!A2)),-1),"no check yet")

 

[Jabe]

Great, that seems to work, thanks hagia_sofia!

 

[Peter_SSs]

Or according to your sample data you could use this to get all rows at once with just this short formula at the top.

Jabe.xlsm
	A	B
1	Name	Last Date Checked
2	Allan	30/05/2024
3	Beverley	22/08/2024
4	Charlie	15/06/2024
Sheet2

Cell Formulas
Range	Formula
B2:B4	B2	=MAXIFS(Sheet1!B2:B8,Sheet1!A2:A8,A2:A4)
Dynamic array formulas.

If it might be that somebody does not yet have a checked date ..

Jabe.xlsm
	A	B
1	Name	Last Date Checked
2	Allan	30/05/2024
3	Tom	N/A
4	Charlie	15/06/2024
Sheet2

Cell Formulas
Range	Formula
B2:B4	B2	=LET(d,MAXIFS(Sheet1!B2:B8,Sheet1!A2:A8,A2:A4),IF(d,d,"N/A"))
Dynamic array formulas.