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

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"))