Lookup and return Heading of column based on criteria (sample data attached)

Hi,

Basically I have columns for each month Jan - Dec (and a total) which hold monitary, or null values.
Next to the total there's two coloumns Forecast Begin and First Month's spend

The formula I need help with is Forecast Begin but the First Month's Spend formula may help some of you with it

First Month's Spend:

Please Login or Register  to view this content.

Somebody wrote this code for me, I've tried to backwards engineer it to help with the Forecast Begin formula but I'm clearly not clever enough

Basically what I need to know, is what month the first forecasted spend is (that is not in any months that have passed).
LINE 13: There are two monitary values over the course of 12 months, first there is £6,000 in March, and next there is £12,266 in July.
Since March has been and gone, I'm only interested in the first monitary value in the future, in this case it is £12,266.
My First Month's Spend formula is correcetly bringing back £12,266 but now I need the Forecast Begin column to display July as that is the month that it is being spent.

I believe there are two routes to achieving this:
1) Find the firist value, much like the First Month's Spend formula does, and then find the month that the value is in
2) Use the First Month's Spend column, as this has already found the value, and then find the month based on that.

My only concern with option 2) is that there may be spends that are identical and therefore, if a LOOKUP or something similar was used, it may cause false results.

There is a sample workbook, with fake, irrelivent data for you guys to use - hope it helps.
Any additional info required please let me know

Many thanks

Try

in O2

=IFERROR(SMALL(IF(($A$1:$L$1>=EOMONTH(TODAY(),-1)+1)*($A2:$L2>0),$A$1:$L$1,""""),1),"No spend")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy down

Originally Posted by JohnTopley

Try

in O2

=IFERROR(SMALL(IF(($A$1:$L$1>=EOMONTH(TODAY(),-1)+1)*($A2:$L2>0),$A$1:$L$1,""""),1),"No spend")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy down

Thank you. It worked to some extent, it had random numbers so I changed the cell formatting to 'Date' and it gives me the short date e.g. 01/05/2018 but they're all the 1st of the month. Is there any way to get in text "May". I altered the cell formatting again to a custom one, just the month, so it now just says 05 for May. I know a way to get the results I want by hiding your formula and then using another formula in the cell i want displayed - but is there a way to integrate it with your solution so I don't have hidden formulae everywhere?

I've rep'd you

Originally Posted by kai.

Thank you. It worked to some extent, it had random numbers so I changed the cell formatting to 'Date' and it gives me the short date e.g. 01/05/2018 but they're all the 1st of the month. Is there any way to get in text "May". I altered the cell formatting again to a custom one, just the month, so it now just says 05 for May. I know a way to get the results I want by hiding your formula and then using another formula in the cell i want displayed - but is there a way to integrate it with your solution so I don't have hidden formulae everywhere?

I've rep'd you

Nevermind! I actually figured it out on my own! I wrapped the formula in TEXT and used the formatting of "MMM" and now it works

I'll mark the thread as SOLVED now. Thanks again!

Format as "mmm-yy"