Return column ref based on last positive value

[SimonHughes]

A	B	C	D	E	Last Invoice Column
32	0	0	45	0	D
12	21	0	0	0	B
0	0	45	32	0	D

<tbody>
</tbody>

Hello, I want to return the column reference when that column contains value greater than £0 so I can see when last a customer was invoiced. Can anyone suggest a formula to do this?

 

[a massive zebra]

In F2: =MID(ADDRESS(1,MAX(IF(A2:E2>0,COLUMN(A2:E2)))),2,1) Enter with CTRL+SHFT+ENTR then copy down.

 

[SimonHughes]

Hello, firstly, thanks for the help. I have changed the formula to suit the actual range but strangely, it is returning column A in many cases and column A is outside of the data selected. Any ideas on why this could be happening? =MID(ADDRESS(1,MAX(IF(D2:AE2>0,COLUMN(D2:AE2)))),2,1)

 

[SimonHughes]

Also, the formula is being entered into column AG

 

[Snakehips]

Simon,

If you have the column letters in row 1 ??

Then maybe....

Excel 2007
	A	B	C	D	E	F	G
1	A	B	C	D	E	F	Last Invoice Column
2	32	0	0	45	0		D
3	12	21	0	0	0		B
4	0	0	45	32	0		D
Sheet4

Cell Formulas
Range	Formula
G2	{=IFERROR(INDEX($A$1:$E$1,1,(LOOKUP(10^10,IF(A2:E2>0,COLUMN(A2:E2),"")))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.

 

[Snakehips]

Or, if you only have the few columns, maybe...

Excel 2007
	A	B	C	D	E	F	G
1							Last Invoice Column
2	32	0	0	45	0		D
3	12	21	0	0	0		B
4	0	0	45	32	0		D
Sheet4

Cell Formulas
Range	Formula
G2	{=IFERROR(CHOOSE((LOOKUP(10^10,IF(A2:E2>0,COLUMN(A2:E2),""))),"A","B","C","D","E","F"),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[SimonHughes]

Hi, Tony, that woks perfectly, many thanks indeed.