Formula to pull most recent month where there is data

[kac1125]

Hello, I have the following data A;H:

Item	Sum of 2/1/2020	Sum of 3/1/2020	Sum of 4/1/2020	Sum of 5/1/2020	Sum of 6/1/2020	Sum of 7/1/2020	Sum of 8/1/2020
A		45​	33​	175​	22​	56​

What i want for Column I is for the formula to tell me what the most recent month with data is. For this example I am looking for I to be Sum of 7/1/2020.
Thank you in advance.

 

[awoohaw]

what version of excel do you have? We cannot tell where column I is? is 8/2/2020 Column H?

 

[shinigamilight]

Try

=LOOKUP(2,1/(B2:H2<>""),B2:H2)

 

[kac1125]

what version of excel do you have? We cannot tell where column I is? is 8/2/2020 Column H?

A	B	C	D	E	F	G	H	I
Item	Sum of 2/1/2020	Sum of 3/1/2020	Sum of 4/1/2020	Sum of 5/1/2020	Sum of 6/1/2020	Sum of 7/1/2020	Sum of 8/1/2020	Last Month with data
A		45	33	175	22	56

Sorry for the confusion. I do not have the access to add the add in to my computer. I am on Office 365. So for column I (Last month with Data) for Item A. I am looking for it to report back sum of 7/1/2020 since this is the last month we see data entered. Hope this helps, and thanks again!

 

[kac1125]

Try

=LOOKUP(2,1/(B2:H2<>""),B2:H2)

Thank you! This worked to get the value of 56, is there a way for it to report back the header month instead? so instead of 56 it would say Sum of 7/1/2020?

 

[iggydarsa]

=INDEX(A1:H1,MAX(IF(A2:H2<>"",COLUMN(A2:H2))))

After you enter the formula instead of typing ENTER, type CTRL+SHIFT+ENTER

 

[kac1125]

=INDEX(A1:H1,MAX(IF(A2:H2<>"",COLUMN(A2:H2))))

After you enter the formula instead of typing ENTER, type CTRL+SHIFT+ENTER

Thanks! That worked perfectly!!!

 

[lrobbo314]

I feel kinda silly. Get so caught up in the new formulas you forget about the old tricks.

I guess the one advantage of this would be that you would still get the correct answer even if the dates weren't in order. But this is overengineered for sure.

=LET(
    tbl,B2:H3,
    fx,LAMBDA(x,FILTER(x,INDEX(tbl,2,0)<>"")),
    df,fx(MAP(
        INDEX(tbl,1,0),
            LAMBDA(x,
                DATEVALUE(TAKE(TEXTSPLIT(x," "),,-1))
            )
        )),
    INDEX(fx(tbl),1,XMATCH(MAX(df),df,0))
)

 

[Fluff]

I am on Office 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

As you have 365 you could also use

=TAKE(FILTER($A$1:$H$1,A2:H2<>""),,-1)