Rolling Month Sales Lookup

MrDB4Excel

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
334
Office Version
  1. 2013
Platform
  1. Windows
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
ABCDEFGHIJKLMNO
1JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberMonth NameSales
2JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuary13,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/AFebruary21,029.31
4March19,651.96
5April12,164.48
6May9,601.00
7June18,552.00
8July15,351.00
9August16,747.49
10September11,851.28
11October12,662.00
12November15,095.75
13December0.00
RollingMonths
Cell Formulas
RangeFormula
A1:L1A1=A2
B2:L2B2=DATE(YEAR(A2),MONTH(A2)+1,1)
A3:L3A3=XLOOKUP(A1,$N$2:$N$13,$O$2:$O$13)
Named Ranges
NameRefers ToCells
lstMonths=RollingMonths!$A$2:$L$2B2, A1


RollingMonthsTesting.xlsx
ABCDEFGHIJKLMNO
1MarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMonth NameSales
2MarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryJanuary13,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/AFebruary21,029.31
4March19,651.96
5April12,164.48
6May9,601.00
7June18,552.00
8July15,351.00
9August16,747.49
10September11,851.28
11October12,662.00
12November15,095.75
13December0.00
RollingMonths
Cell Formulas
RangeFormula
A1:L1A1=A2
B2:L2B2=DATE(YEAR(A2),MONTH(A2)+1,1)
A3:L3A3=XLOOKUP(A1,$N$2:$N$13,$O$2:$O$13)
Named Ranges
NameRefers ToCells
lstMonths=RollingMonths!$A$2:$L$2B2, A1
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Rows 1 and 2 contain proper dates that are formatted, your list in column N contains text names of the months. Both need to be the same for a formula to compare them.
Book1
ABCDEFGHIJKLMNO
1Month NameSales
2JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuary13600
31360021029.3119651.9612164.489601185521535116747.4911851.281266215095.75February21029.31
4March19651.96
5April12164.48
6May9601
7June18552
8July15351
9August16747.49
10September11851.28
11October12662
12November15095.75
13December0
Sheet2
Cell Formulas
RangeFormula
B2:L2B2=EDATE(A2,1)
A3:L3A3=IFERROR(1/(1/XLOOKUP(TEXT(A2:L2,"mmmm"),$N$2:$N$13,$O$2:$O$13)),"")
Dynamic array formulas.
 
Upvote 0
Not sure what I am doing wrong so here is an Xl2bb paste.
RollingMonthsTesting.xlsx
ABCDEFGHIJKLMNO
1Month NameSales
2JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuary$13,600.00
3            February$21,029.31
4March$19,651.96
5April$12,164.48
6May$9,601.00
7June$18,552.00
8July$15,351.00
9August$16,747.49
10September$11,851.28
11October$12,662.00
12November$15,095.75
13December$0.00
RollingMonths
Cell Formulas
RangeFormula
B2:L2B2=EDATE(A2,1)
A3:L3A3=IFERROR(1/(1/XLOOKUP(TEXT($A$2:$L$2,"mmmm"),$N$2:$N$13,$O$2:$O$13)),"")
Named Ranges
NameRefers ToCells
lstMonths=RollingMonths!$A$2:$L$2B2, A3
 
Upvote 0
Something has changed, or it posted wrong in the first sheet. The months in column N were showing as text in post 1 but are now showing as proper dates with formatting in post 3.
Comparing the date serial numbers, I can see that row 2 starts from 1 Jan 2022 while column N starts from 1 Jan 2021
With that in mind, your original xlookup formula was effectively correct but there needs to be some consistency in the dates for it (or any other formula) to work properly.
The formula below includes the change to hide any 0 value results but the dates will still need to be changed so that they match.
Excel Formula: 
=IFERROR(1/(1/XLOOKUP($A$2:$L$2,$N$2:$N$13,$O$2:$O$13)),"")
 
Upvote 0
This must be my dense day. I do not follow what you are saying in your recent reply:
NameRefers ToCells
Named Ranges
lstMonths=RollingMonths!$A$2:$L$2B2, A3

Not in the least. Treat me like this is my first day ever using Excel although I have more than 20 years using Excel.
Also is the formula =IFERROR(1/(1/XLOOKUP(TEXT($A$2:$L$2,"mmmm"),$N$2:$N$13,$O$2:$O$13)),"") to be added into all cells A3 to L3?
 
Upvote 0
Would having A2 be a Data Validation List mess with proper function?
So, based on the attached Xl2bb which formula is correct?
=IFERROR(1/(1/XLOOKUP($A$2:$L$2,$N$2:$N$13,$O$2:$O$13)),"")
or
=IFERROR(1/(1/XLOOKUP(TEXT($A$2:$L$2,"mmmm"),$N$2:$N$13,$O$2:$O$13)),"")

RollingMonthsTesting.xlsx
ABCDEFGHIJKLMNO
1Month NameSales
2JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuary$13,600.00
3            February$21,029.31
4March$19,651.96
5April$12,164.48
6May$9,601.00
7June$18,552.00
8July$15,351.00
9August$16,747.49
10September$11,851.28
11October$12,662.00
12November$15,095.75
13December$0.00
RollingMonths
Cell Formulas
RangeFormula
B2:L2B2=EDATE(A2,1)
A3A3=IFERROR(1/(1/XLOOKUP($A$2:$L$2,$N$2:$N$13,$O$2:$O$13)),"")
B3:L3B3=IFERROR(1/(1/XLOOKUP(TEXT($A$2:$L$2,"mmmm"),$N$2:$N$13,$O$2:$O$13)),"")
Named Ranges
NameRefers ToCells
lstMonths=RollingMonths!$A$2:$L$2A3, B2
 
Upvote 0
MrDB4Excel said:
Also is the formula =IFERROR(1/(1/XLOOKUP(TEXT($A$2:$L$2,"mmmm"),$N$2:$N$13,$O$2:$O$13)),"") to be added into all cells A3 to L3?
Click to expand...
No, it only needs to be in A3. The rest (B3:L3) need to be empty. If you have entered the formula into all of them then that would explain why it is currently showing a blank, although you will need to use the formula without the TEXT function for it to work.
Excel Formula: 
=IFERROR(1/(1/XLOOKUP($A$2:$L$2,$N$2:$N$13,$O$2:$O$13)),"")
 
Upvote 0
This is the result and still not working.
RollingMonthsTesting.xlsx
ABCDEFGHIJKLMNO
1Month NameSales
2JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuary$13,600.00
3 February$21,029.31
4March$19,651.96
5April$12,164.48
6May$9,601.00
7June$18,552.00
8July$15,351.00
9August$16,747.49
10September$11,851.28
11October$12,662.00
12November$15,095.75
13December$0.00
RollingMonths
Cell Formulas
RangeFormula
B2:L2B2=EDATE(A2,1)
A3A3=IFERROR(1/(1/XLOOKUP($A$2:$L$2,$N$2:$N$13,$O$2:$O$13)),"")
Named Ranges
NameRefers ToCells
lstMonths=RollingMonths!$A$2:$L$2A3, B2
 
Upvote 0
That should be working fine, I get the correct results from that. Do you have the correct version of excel to be using XLOOKUP? I was using the same functions as your original formula but have noticed that your profile says excel 2013. XLOOKUP requires either office 365 or excel 2021. I was assuming that you have a newer version from the #N/A errors in post 1 where I would have expected #NAME? if you tried that formula in 2013.

If you still have 2013 then you will need to use vlookup instead.
Excel Formula: 
=IFERROR(1/(1/VLOOKUP(A$2,$N$2:$O$13,2,0)),"")
 
Upvote 0
Vlookup works, but I have used Xlookup in 2013 consistently for ages. Here is a recent post regarding my use of Xlookup from this post:

Get Month from a Column Based on Specific Text in an Adjacent Column​

Easy. ExcelDna.XFunctions64 found here: exceldna.xfunctions64 at DuckDuckGo

how to use xlookup in excel 2013 at DuckDuckGo​

DuckDuckGo. Privacy, Simplified.
duckduckgo.com

Xlookup Excel Add In Excel 2013​

Xlookup Excel Add In Excel 2013! xlookup download excel ,tutorial excel, step by step excel, how to use excel
www.how-use-excel.com
www.how-use-excel.com
I don't remember exactly what site I visited to get this to work but the above links should help.
Also this: Free XLOOKUP XFuntion Excel Addin For All Versions of Excel
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VLookup Struggles
Replies
7
Views
284
Peter_SSs
Peter_SSs
W
  • Question
Personnel Roster using Macro's
Replies
0
Views
171
womeclr
W
S
  • Solved
need to remove all data after "n"
Replies
2
Views
298
sksanjeev786
S
E
  • Solved
Macro stops working before end sub with no prompt
2 3
Replies
21
Views
1K
_eNVy_
E
C
  • Solved
Lookup 3 conditions on different sheet
Replies
4
Views
66
awoohaw
awoohaw

Forum statistics

Threads
1,214,648
Messages
6,120,726
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top