Find the most recent entry in a table

[fysty0102]

Hi all,

I have a formula (TEMPLATE O247)

=IFNA(XLOOKUP(1,('IT0015'!$A:$A=$B$1)*('IT0015'!$X:$X=J252),('IT0015'!$Z:$Z)),0)

I want to add that if the "Changed On" date is between Nov 1 & 15th to use the amount but if it's not, leave as 0

for (TEMPLATE O249)

=IFNA(XLOOKUP(1,('IT0014'!$A:$A=$B$1)*('IT0014'!$X:$X=J254),('IT0014'!$Z:$Z)),0)

if IT0014 has an end date of 9999-12-31 OR start/end dates are between Nov 1-15 then return that value but if it's not, leave as 0

[azumi]

Try to upload your excel file to identify the problem more clear

[fysty0102]

My deepest apologies. I thought I had.

[RhiSJ]

O247

=IFERROR(INDEX(SORT(FILTER('IT0015'!I1:L5000,

('IT0015'!A1:A5000=B1)*('IT0015'!J1:J5000=M247)

*(TEXT('IT0015'!I1:I5000,"YYMM")="2411")

*(TEXT('IT0015'!I1:I5000,"dd")<="15")

),1,-1),1,4), 0)

O249

=IFERROR(INDEX(SORT(FILTER('IT0014'!I1:L5000,

('IT0014'!A1:A5000=B1)*('IT0014'!J1:J5000=M249)*

((TEXT('IT0014'!F1:F5000,"YYMMDD")="991231")

+((TEXT('IT0014'!F1:F5000,"YYMMDD")="2411")*(TEXT('IT0014'!F1:F5000,"dd")<="15"))

+((TEXT('IT0014'!G1:G5000,"YYMMDD")="2411")*(TEXT('IT0014'!G1:G5000,"dd")<="15"))

) ),1,-1),1,4), 0)

[fysty0102]

Worked perfectly!

[fysty0102]

@RhiSJ

Worked perfectly!! May I throw another variable in? Nov 1-16 is PP 22-2024 (A214:A273). How do we adjust the date look up so when I copy down to PP 23-2024 (A274:A325), it takes Nov 16-30 without manually adjusting the formula dates?

[RhiSJ]

P247

=IFERROR(INDEX(SORT(FILTER('IT0015'!$I$1:$L$5000,

('IT0015'!$A$1:$A$5000=B1)*('IT0015'!$J$1:$J$5000=M247)

*('IT0015'!$I$1:$I$5000>=MIN(B214:B273))

*('IT0015'!$I$1:$I$5000<=MAX(B214:B273))

),1,-1),1,4), 0)

p249

=IFERROR(INDEX(SORT(FILTER('IT0014'!$I$1:$L$5000,

('IT0014'!$A$1:$A$5000=B1)*('IT0014'!$J$1:$J$5000=M249)*

(('IT0014'!$F$1:$F$5000=DATEVALUE("2099-12-31"))

+('IT0015'!$I$1:$I$5000>=MIN(B214:B273))

+('IT0015'!$I$1:$I$5000<=MAX(B214:B273))

) ),1,-1),1,4), 0)

[shukla.ankur281190]

Or can try

O247

=IFERROR(LOOKUP(2,1/(('IT0015'!A1:A5000=B1)*('IT0015'!J1:J5000=M247)*(DAY('IT0015'!I1:I5000)<="15")),'IT0015'!L1:L5000),0)

O249

=IFERROR(LOOKUP(99+99,1/(('IT0014'!A2:A5000=B1)*('IT0014'!J2:J5000=M249)*((('IT0014'!F2:F5000=DATE(9999,12,31)))+((DAY('IT0014'!F2:F5000)<=15)+((DAY('IT0014'!G2:G5000)<=15))))),'IT0014'!L2:L5000),0)

[fysty0102]

@shukla.ankur281190

This one worked better in my live workbook Thank you

[RhiSJ]

O247 returns 105, it's 'ChangedOn' is 2024-10-14, not november.
This formula cannot match month or year