July 28, 2023
October 5, 2010
There's no file attached with data to test this on so hard to say.
If I use that formula in an empty workbook, that has a sheet called Legends, it returns 0. So syntactically the formula is ok. Must be a data issue.
If I fill in dummy data to the ranges in your formula, I get #N/A if N6 is empty or contains a value that isn't in the range Legends!$A23:$A47
If that hasn't helped fix it, please attach a file with the data this XLOOKUP is using.