Datevalue returns #N/A

[felixkk07]

Hi,

The formula at cell no. 9C worked well in my past spreadsheets.

However, it returns #N/A today.

Please advise what is wrong with the formula.

Thanks a lot!

	A	B	C
9	2022-10-10	10 October 2022	#N/A
		=TEXT(A9,"dd")& TEXT(A9," mmmm")& TEXT(A9," yyyy")	=LOOKUP(DATEVALUE(B9),DATEVALUE(a13:b13),c13:c13)
13	16/9/2022	15/10/2022	Mid-Oct 2022
	=TEXT("16/9/2022","dd/mm/yyyy")	=TEXT("15/10/2022","dd/mm/yyyy")

 

[Joe4]

Welcome to the Board!

#N/A simply denotes that it is not finding a match.

If you cannot figure out why, please post a sample of your data that includes the matching values.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.