Rolling Month Sales Lookup

[MrDB4Excel]

I do not understand why these formulas aren’t working.
This first Xl2bb mini sheet shown represents a bogus sales chart. Cell A2 is a Data Validation List.
When the month is changed in A2 then all the following Row 2 cells change according to the formulas you see in B2:L2 as shown in the second Xl2bb mini-sheet.
My formulas in A3 to L3 need to get the month name from A1 to L1 as the Data Validation List changes, but it does not work.
Does anyone have a notion why this is failing?
Also if a value in O2:O13 is 0.00 then the relevant cell in A3:L3 needs to remain blank.
Any help is much appreciated as I have spent the better part of 4 hours trying to make it work.

RollingMonthsTesting.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	January	February	March	April	May	June	July	August	September	October	November	December		Month Name	Sales
2	January	February	March	April	May	June	July	August	September	October	November	December		January	13,600.00
3	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A		February	21,029.31
4														March	19,651.96
5														April	12,164.48
6														May	9,601.00
7														June	18,552.00
8														July	15,351.00
9														August	16,747.49
10														September	11,851.28
11														October	12,662.00
12														November	15,095.75
13														December	0.00
RollingMonths

Cell Formulas
Range	Formula
A1:L1	A1	=A2
B2:L2	B2	=DATE(YEAR(A2),MONTH(A2)+1,1)
A3:L3	A3	=XLOOKUP(A1,$N$2:$N$13,$O$2:$O$13)

Named Ranges
Name	Refers To	Cells
lstMonths	=RollingMonths!$A$2:$L$2	B2, A1

RollingMonthsTesting.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	March	April	May	June	July	August	September	October	November	December	January	February		Month Name	Sales
2	March	April	May	June	July	August	September	October	November	December	January	February		January	13,600.00
3	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A		February	21,029.31
4														March	19,651.96
5														April	12,164.48
6														May	9,601.00
7														June	18,552.00
8														July	15,351.00
9														August	16,747.49
10														September	11,851.28
11														October	12,662.00
12														November	15,095.75
13														December	0.00
RollingMonths

Cell Formulas
Range	Formula
A1:L1	A1	=A2
B2:L2	B2	=DATE(YEAR(A2),MONTH(A2)+1,1)
A3:L3	A3	=XLOOKUP(A1,$N$2:$N$13,$O$2:$O$13)

Named Ranges
Name	Refers To	Cells
lstMonths	=RollingMonths!$A$2:$L$2	B2, A1

 

[jasonb75]

If vlookup works for you and xlookup works for me but not for you then it would mean a problem with the add in that you're using.

If you want that fixed then you would need to go back to whoever created the add in, that is not something that I can help with.

 

[MrDB4Excel]

Thanks for all your help.
I get the feeling that it is this part: 1/(1/XLOOKUP that messes with it because all other instances of Xlookup work with my addin.

 

[jasonb75]

I get the feeling that it is this part: 1/(1/XLOOKUP that messes with it because all other instances of Xlookup work with my addin.

If the xlookup is working then that part should work, the exception being if the sales amounts are not valid numbers, the easy way to test it is to try xlookup without that part (as you had in post 1).
Possibly the xlookup add in doesn't work correctly with dates, or the parameters are not the same as the proper version.

 

[MrDB4Excel]

I will just have to play around with it to see if I can get xlookup to work, otherwise vlookup will suffice.
Thanks again