You’re using a lookup formula (VLOOKUP or XLOOKUP). You’re looking for a date down a column and the formula returns #NA. You know that the date you’re searching for exists in the lookup column (even though the date formats are different, a date is a date is a date).
However, the values in column A are not dates. They are text entries that look like dates whereas what’s in H1 is a real date. 15.09.2024 is not the same as 15 September 2024.
In this video I explain how to be able to identify that the values in column A are not dates and then I show how to use the Text-to-Columns feature in Excel to fix the problem.
Download a copy of the file used in this video: https://share.zight.com/7KuNY26y