Need to alter a formula to dynamically look for data in a column based on month selected

[Gamer85308]

Hello and thanks in advance for any help. I am using Office 365.

I have this formula which works well but I need to improve it:

=IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(Data!I5:I144<40000)*(Data!J5:J144<40000)*(Data!K5:K144<40000)*(Data!L5:L144<40000),""),1,4,5,8,9,10,11),"No accounts")

The piece I need to improve is this:

Data!I5:I144<40000)*(Data!J5:J144<40000)*(Data!K5:K144<40000)*(Data!L5:L144<40000),"")

The issue is that each section in the second code piece points at one column non-dynamically. For example, I5:I144 points to the month of January.  Please see the image below. When May becomes June, the columns that now show Jan-April will change to Feb-May. I want the data below to change with it. How do I do this? I tried using XMATCH but it would not accept the "<40000" piece. Any suggestions?

Thank you again for any help

[#2]

I don't understand the question because it is unclear what you mean by "when may becomes June, the columns that now show Jan-April will change to Feb-May" and what you need the formula to change to. That said however, here is an idea/concept that might help. You already use the CHOOSECOLS() function and you can use that instead of the direct reference so you can then have a parameter like 'offset' that picks which column to start the check.
=IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(CHOOSECOLS(Data!I5:M144,0+offset)<40000)*(CHOOSECOLS(Data!I5:M144,1+offset)<40000)*(CHOOSECOLS(Data!I5:M144,2+offset)<40000)*(CHOOSECOLS(Data!I5:M144,3+offset)<40000),""),1,4,5,8,9,10,11),"No accounts")

[#3]

Hello again mtarler, So I have four columns headers that look like this:
Jan Feb Mar Apr
The formula in the April column looks like this: =TEXT(EDATE(TODAY(),-1),"Mmm")
The formula in the Mar column looks like this: =TEXT(EDATE(TODAY(),-2),"Mmm")
The formula in the Feb column looks like this: =TEXT(EDATE(TODAY(),-3),"Mmm")
The formula in the Jan column looks like this: =TEXT(EDATE(TODAY(),-4),"Mmm")
The April column is this month minus one so when May becomes June, April will become May on the column header.

What does +OFFSET do? I'll play with it. Thanks for your help!

[#4]

I added the formula as follows and I had to edit some of the cell references

=IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(CHOOSECOLS(Data!G5:T144,0+offset)<40000)*(CHOOSECOLS(Data!G5:T144,1+offset)<40000)*(CHOOSECOLS(Data!G5:T144,2+offset)<40000)*(CHOOSECOLS(Data!G5:T144,3+offset)<40000),""),1,4,5,8,9,10,11),"No accounts")

It is returning No accounts so something is wrong

[#5 · Accepted solution]

I still don't get what/why you are doing this. The "offset" I put in just as a place holder. so if you want it to always point to 'Apr' column then maybe use Match(). or you can base it on the Month in cell D4.
You can use LET() to make it easier if you want so then you 'define' offset in the let statement and then use it in the rest. I am not claiming the following Match is the right value for the offset, just an idea for you to work with:
=LET(offset, Match(text(edate(today(),-1),"Mmm"),Data!G4:T4,0),
IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(CHOOSECOLS(Data!G5:T144,0+offset)<40000)*(CHOOSECOLS(Data!G5:T144,1+offset)<40000)*(CHOOSECOLS(Data!G5:T144,2+offset)<40000)*(CHOOSECOLS(Data!G5:T144,3+offset)<40000),""),1,4,5,8,9,10,11),"No accounts"))

[#6]

The why. I created this for my team and I know how to work it. I will be handing it to three other teams and each copy will be managed by someone on that team who has no Excel experience or not much at all. Gathering the data for the data and shipments tabs takes me about two hours. If I have to ask them to update cells every month I don't believe they will do it. The harder I make it, the less likely it is they will use it. The value to the teams cannot be overstated and I have to automate this as much as possible so it won't overwhelm clueless users who manage it.

I don't understand how inserting the word "offset" in the formula helps at all but I will mark this as solved as I am not making any headway here and no one else is offering any better solutions

[#7]

I get it, I'm reading about LET

[#8]

Yes, LET is VERY powerful and VERY useful. Definitely learn LET and then learn LAMBDA and change this to be a LAMBDA function so in each cell you can something easy like =MyFunction()
BTW, in hindsight the name "offset" was probably a poor choice by me since it is a built in Excel function name already.

[#9]

Those are good ideas and I will pursue them. FYI - I plugged your Let formula into my sheet and it worked! Thanks for all your help