Return largest value from other variable

[Darkrun]

Hello All,

I've been having an issue trying to automate this sheet. I am trying to have it so that as I change the 'date to check' the Area in column C will update to the N column which has the highest date in column M, either before or equal to the new date in G1, if the Property code isn't in the L:N, to then return D:D. Column B will also show the date that is either the equal to or the highest under the G1 date.

I hope this makes sense and any help would be greatly appreciated.

 

[felixstraube]

Would this work?:

I didn't quite understand what you want to show if the code isn't found. In C2 show content of D2? And in B2 show the date in G1?

Book5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Property code	Date	Area	Original Area		Date to check	05/03/2023					Property code	Date of change	Area
2	7145377	code not found	code not found	1126								71453778	06/05/2023	1345
3												71453778	01/06/2023	2346
4												71453778	04/03/2024	4645
REturn largest value

Cell Formulas
Range	Formula
B2	B2	=LET( pc,$L$2:$L$1000, d,$M$2:$N$1000, fd,FILTER(d,pc=$A$2), IFERROR(XLOOKUP($G$1,CHOOSECOLS(fd,1),CHOOSECOLS(fd,1),"date not found",1), "code not found") )
C2	C2	=LET( pc,$L$2:$L$1000, d,$M$2:$N$1000, fd,FILTER(d,pc=$A$2), IFERROR(XLOOKUP($G$1,CHOOSECOLS(fd, 1),CHOOSECOLS(fd, 2),"date not found", 1), "code not found") )

 

[Scott Huish]

@felixstraube That doesn't seem to check if the result is less than or equal to the date in G3

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Property code	Date	Area	Original Area		Date to check	3/5/2023					Property code	Date of change	Area
2	71453778	5/6/2023	1345	1126								71453778	5/6/2023	1345
3												71453778	6/1/2023	2346
4												71453778	3/4/2024	4645
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LET( pc,$L$2:$L$1000, d,$M$2:$N$1000, fd,FILTER(d,pc=$A$2), IFERROR(XLOOKUP($G$1,CHOOSECOLS(fd,1),CHOOSECOLS(fd,1),"date not found",1), "code not found") )
C2	C2	=LET( pc,$L$2:$L$1000, d,$M$2:$N$1000, fd,FILTER(d,pc=$A$2), IFERROR(XLOOKUP($G$1,CHOOSECOLS(fd, 1),CHOOSECOLS(fd, 2),"date not found", 1), "code not found") )

 

[Scott Huish]

How about:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Property code	Date	Area	Original Area		Date to check	3/5/2023					Property code	Date of change	Area
2	71453778	3/4/2023	2346	1126								71453780	3/5/2023	1345
3												71453778	3/4/2023	2346
4												71453778	2/19/2023	4645
5
Sheet1

Cell Formulas
Range	Formula
B2:C2	B2	=IFERROR(LET(a,FILTER($L$2:$N$4,($M$2:$M$4<=$G$1)*($L$2:$L$4=A2)),DROP(FILTER(a,INDEX(a,,2)=MAX(INDEX(a,,2))),,1)),"not found")
Dynamic array formulas.

 

[felixstraube]

@felixstraube That doesn't seem to check if the result is less than or equal to the date in G3

Yes, you are right. I had it wrong.
I think now it works as OP asked.

Book5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Property code	Date	Area	Original Area		Date to check	01/06/2023					Property code	Date of change	Area
2	71453778	01/06/2023	2346	1126								71453778	06/05/2023	1345
3												71453778	01/06/2023	2346
4												71453778	04/03/2024	4645
REturn largest value

Cell Formulas
Range	Formula
B2:C2	B2	=LET( pc,$L$2:$L$1000, d,$M$2:$N$1000, fd,FILTER(d,pc=$A$2), IFERROR(XLOOKUP($G$1,CHOOSECOLS(fd,1),fd,"date not found",-1), "code not found") )
Dynamic array formulas.