Lookup based on 2-way matching with an embedded date condition

[chris_bosten]

Dear sir/madam

I am making a training matrix and am struggling with a formula.

I have one tab called "training module entry" where trainers can input when a piece of training has been completed.
A B C D E F G H

Date of Training	Employee Name	Employee ID	Module Name	Module ID	Frequency	Trainer	Valid to
28/11/2023	X1	EID0001	Manual Handling	PATR039	Annual	H	28/11/2024
28/11/2024	X2	EID0006	Manual Handling	PATR039	Annual	H	28/11/2025
28/11/2024	X3	EID0004	Manual Handling	PATR039	Annual	H	28/11/2025
28/11/2024	X4	EID0008	Manual Handling	PATR039	Annual	H	28/11/2025
28/12/2023	X5	EID0050	Manual Handling	PATR039	Annual	H	28/12/2024
28/11/2024	X6	EID0032	Yard Safety Waterswallows	PATR025	Annual	H	28/11/2025
28/11/2024	X7	EID0012	Technosys SSoW	PATR012	One-off	H	No End Date

Depending on which module has been selected, the valid to date will adjust.

I then have a second tab for the training matrix:
A B C D E F G H I J K L M N O P Q

Mixing and Packing

Mixing and Packing Specialism

Training Valid To

LOTO

Silo 8 Bagger SSoW

Silo 2 SSoW

Silo 10 SSoW

Air Packer SSoW

Boone Mixer 1 SSoW

Boone Mixer 2 SSoW

Heating Plant SSoW

Silo 9 SSoW

MIS Bagging

MIS Mixing

Technosys SSoW

Powders Shift Routine

Yard Safety Waterswallows

Manual Handling

Name

Employee ID

PATR001

PATR002

PATR003

PATR004

PATR005

PATR006

PATR007

PATR008

PATR009

PATR010

PATR011

PATR012

PATR013

PATR025

PATR039

X5

EID0050

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

28/12/2024

X6

EID0008

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

28/11/2025

X7

EID0004

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

No Record

28/11/2025

I want the cells in this matrix to return a valid to date based on a 2-way match of employee ID and module ID. If no match can be found I want it to return "No Record." I have done this in cell Q4 with the following formula:

=FILTER('Training Module Entry'!$H$2:$H$10000,('Training Module Entry'!$C$2:$C$10000='M&P Matrix'!B4)*('Training Module Entry'!$E$2:$E$10000='M&P Matrix'!Q3),"No Record").

I have 2 problems. Firstly, if an individual with a unique employee ID has multiple training records uploaded for the same training module, I want the expiry date to be based on the module entry with the most recent "date of training" date.

Secondly, if the expiry date is greater than a month after today's date, I want the cell to return "expired"

Can you help?

Best wishes
Chris

 

[chris_bosten]

I have done a workaround - so only answer this if you fancy it!

Best wishes
Chris

 

[chris_bosten]

I have a simpler problem. I'm highlighting duplicate values in a column, but if the cells are blank I don't want any highlight - how do I achieve this in conditional formatting?

 

[Alex Blakenburg]

You might want to make that a separate thread.
In relation to your first thread the "Expired" logic seems to be back to front. Surely if the Valid to date is greater than today is hasn't expired.

This is my thinking on it:

20241204 Filter Max Date chris_bosten.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		LOTO	Silo 8 Bagger SSoW	Silo 2 SSoW	Silo 10 SSoW	Air Packer SSoW	Boone Mixer 1 SSoW	Boone Mixer 2 SSoW	Heating Plant SSoW	Silo 9 SSoW	MIS Bagging	MIS Mixing	Technosys SSoW	Powders Shift Routine	Yard Safety Waterswallows	Manual Handling
3	Name	Employee ID	PATR001	PATR002	PATR003	PATR004	PATR005	PATR006	PATR007	PATR008	PATR009	PATR010	PATR011	PATR012	PATR013	PATR025	PATR039
4	X5	EID0050	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	28/12/2024
5	X6	EID0008	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	28/11/2025
6	X7	EID0004	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	28/11/2025
7	X1	EID0001	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	28/11/2024
8	X9	EID0012	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	No Record	28/11/2025	No Record	No Record	No Record
M&P Matrix

Cell Formulas
Range	Formula
C4:Q8	C4	=LET(arrFltr,FILTER('Training Module Entry'!$H$2:$H$10000,('Training Module Entry'!$C$2:$C$10000=$B4)*('Training Module Entry'!$E$2:$E$10000=C$3),"No Record"), ValidTo,IF(COUNTA(arrFltr)>1,MAX(arrFltr),arrFltr), IF(ISNUMBER(ValidTo),IF(ValidTo<(EDATE(TODAY(),-1)),"Expired",ValidTo),ValidTo))

 

[chris_bosten]

=LET(arrFltr,FILTER('Training Module Entry'!$H$2:$H$10000,('Training Module Entry'!$C$2:$C$10000=$B4)*('Training Module Entry'!$E$2:$E$10000=C$3),"No Record"), ValidTo,IF(COUNTA(arrFltr)>1,MAX(arrFltr),arrFltr), IF(ISNUMBER(ValidTo),IF(ValidTo<(EDATE(TODAY(),-1)),"Expired",ValidTo),ValidTo))

Hi, yes you were right about that logic, need my morning coffee!

 

[chris_bosten]

Hi Alex, I gave it a go but found that an entry that should have returned "expired" was showing as "no record" - which cell should I copy that formula into?