Caculate the most recent month a customer made a purchase

[MikeGeno]

I have a spreadsheet with months listed chronologically across columns and customers listed down the rows.



Customer Month1 Month2 Month3 Most Recent Month Purchased
ABC
BCD
CDE


I would like to put a formula in the Most Recent Month Purchased that will give me the date of the last month that a customer made a purchase.

In the example Above is Customer ABC purchased in Month 1 and Month 2 but not Month 3 I would like the answer to show Month 2.

I thought of using a Nested If statement but I have 48 months of data and it would be a very unwieldy formula.

Any suggestions would be most appreciated

 

[Andrew Poulsom]

Try:

=INDEX(B$1:D$1,MATCH(9.99999999999999E+307,B2:D2))

Expand the ranges to suit.

 

[MikeGeno]

It seems to always return the most recent column no matter what data is present.

In some instances I may have an account with Sales in month 1, none in month 2, Sales in Month 3, and None in Month 4 (Added a month here to show the example.

In this case I would want Month 3 to be the answer. Basically I am looking for the right most column with a non $0

 

[MikeGeno]

=IF(AS2<>0,AS$1,IF(AR2<>0,AR$1,IF(AQ2<>0,AQ$1,IF(AP2<>0,AP$1,IF(AO2<>0,AO$1,IF(AN2<>0,AN$1,IF(AM2<>0,AM$1,IF(AL2<>0,AL$1,IF(AK2<>0,AK$1,IF(AJ2<>0,AJ$1,IF(AI2<>0,AI$1,IF(AH2<>0,AH$1,IF(AG2<>0,AG$1,IF(AF2<>0,AF$1,IF(AE2<>0,AE$1,IF(AD2<>0,AD$1,IF(AC2<>0,AC$1,IF(AB2<>0,AB$1,IF(AA2<>0,AA$1,IF(Z2<>0,Z$1,IF(Y2<>0,Y$1,IF(X2<>0,X$1,IF(W2<>0,W$1,IF(V2<>0,V$1,IF(U2<>0,U$1,IF(T2<>0,T$1,IF(S2<>0,S$1,IF(AQ2<>0,AQ$1,IF(P2<>0,P$1,IF(O2<>0,O$1,IF(N2<>0,N$1,IF(M2<>0,M$1,IF(L2<>0,L$1,IF(K2<>0,K$1,IF(J2<>0,J$1,IF(I2<>0,I$1,IF(H2<>0,H$1,IF(G2<>0,G$1,IF(F2<>0,F$1,F$1)))))))))))))))))))))))))))))))))))))))

Gave in and wrote the 40 time nested if statement to find the first month from right to left that has a number other than 0.

 

[Skybluekid]

Try this Use Control shift enter not just enter

Excel Workbook
	B	C	D	E	F	G	H
3
4	Customer	01-Feb	01-Mar	01-Apr	01-May
5	ABC	x	x		x	01 May
6	DEF		x	x		01 Apr
7	GHI	x	x			01 Mar
8
9
Sheet1

 

[Aladin Akyurek]

It seems to always return the most recent column no matter what data is present.

In some instances I may have an account with Sales in month 1, none in month 2, Sales in Month 3, and None in Month 4 (Added a month here to show the example.

In this case I would want Month 3 to be the answer. Basically I am looking for the right most column with a non $0

If $0 entries really exist, Andrew's proposal requires some modification...

=LOOKUP(9.99999999999999E+307,1/B2:D2,B$1:D$1)

 

[T. Valko]

It seems to always return the most recent column no matter what data is present.

In some instances I may have an account with Sales in month 1, none in month 2, Sales in Month 3, and None in Month 4 (Added a month here to show the example.

In this case I would want Month 3 to be the answer. Basically I am looking for the right most column with a non $0

Try this...

B1:Z1 = column headers
B2:Z2 = data

=LOOKUP(1E100,1/B2:Z2,B$1:Z$1)

 

[MikeGeno]

Thank you everyone for the help most appreciated! All of the last 3 solutions worked and much easier than my 40 nested ifs.