Timestamp ignore - Google Sheets

[Keshek]

=IFNA((VLOOKUP(DATE(Q$1,MONTH($P2),DAY($O2)),'Input Data'!$A:$C,3,FALSE)),Q3)
Is it possible for this to ignore the timestamp, if the range also has timestamp included in column A?

As in it looks like this 31/10/2020 12:00:00.

Google sheets btw.

 

[excel_learnerz]

Yes, you can modify the formula to ignore the timestamp by using the INT function to extract only the date part of the datetime value in column A.

Here's the modified formula:

=IFNA((ARRAYFORMULA(VLOOKUP(DATE(Q$1,MONTH($P2),DAY($O2)),{INT('Input Data'!$A:$A), 'Input Data'!$B:$C},3,FALSE))),Q3)


This formula uses the ARRAYFORMULA function to apply the INT function to the entire 'Input Data'!$A:$A range, effectively removing the time portion of the datetime values. The rest of the formula remains the same as before, using the VLOOKUP function to search for the desired date and return the corresponding value from column C.

 

[Fluff]

Another option that may work in Sheets.

=IFNA(INDEX('Input Data'!$C$2:$C$1000,MATCH(DATE(Q$1,MONTH($P2),DAY($O2)),INT('Input Data'!$A$2:$A$1000),0)),Q3)