Return column ref based on last positive value

SimonHughes

Active Member
Joined
Sep 16, 2009
Messages
382
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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

a massive zebra

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

SimonHughes

Active Member
Joined
Sep 16, 2009
Messages
382
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)
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Simon,

If you have the column letters in row 1 ??

Then maybe....
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">A</td><td style="text-align: center;;">B</td><td style="text-align: center;;">C</td><td style="text-align: center;;">D</td><td style="text-align: center;;">E</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Last Invoice Column</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">32</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">45</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">D</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">12</td><td style="text-align: center;;">21</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">B</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">45</td><td style="text-align: center;;">32</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">D</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$1:$E$1,1,(<font color="Green">LOOKUP(<font color="Purple">10^10,IF(<font color="Teal">A2:E2>0,COLUMN(<font color="#FF00FF">A2:E2</font>),""</font>)</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />Hope that helps.
 
Last edited:

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
Or, if you only have the few columns, maybe...
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Last Invoice Column</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">32</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">45</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">D</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">12</td><td style="text-align: center;;">21</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">B</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">45</td><td style="text-align: center;;">32</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">D</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">{=IFERROR(<font color="Blue">CHOOSE(<font color="Red">(<font color="Green">LOOKUP(<font color="Purple">10^10,IF(<font color="Teal">A2:E2>0,COLUMN(<font color="#FF00FF">A2:E2</font>),""</font>)</font>)</font>),"A","B","C","D","E","F"</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,177
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top