Return the column header of last cell with data

1
votes

I'm working on a spreadsheet and trying to create a formula to count the last cell with data, then return the column heading for that cell.

There are two parts here:

  1. Find the first cell with data, return the column heading
  2. Find the last cell with data, return the column heading

I've solved for 1 by the following: 

=INDIRECT(ADDRESS(1,COLUMN((INDEX(H223:S223,MATCH(TRUE,INDEX((H223:S223<>0),0),0))))))

But I haven't been able to solve for the second part. I found the following solutions online, but they aren't quite right...

=(ADDRESS(ROW(H223:S223)+ROWS(H223:S223)-1,COLUMN(H223:S223)+COLUMNS(H223:S223)-1))

^ Returns the last cell reference of the range, does not check if cell has data or not. 

=LOOKUP(2,1/(H228:S228<>""),H228:S228)

^This one is a bit more promising. This formula returns the value of the last cell with data in the range. But I can't get LOOKUP to work with INDIRECT/ADDRESS/COLUMN functions which I used for the first formula.

Can anyone help me out? Thanks!

FYI - the column headings are months and cells are $ projections. These formulas are supposed to help me understand how $ flows by month for the next fiscal year.

2
excelexcel-formulaexcel-indirect

2 Answers

0
votes
=LOOKUP(2,1/(H228:S228<>""), H228:S228)
'                            ^^^^^^^^^^

Your LOOKUP formula is a good idea, but needs a slight modification to return a value from the header row instead of the investigated row:

=LOOKUP(2,1/(H228:S228<>""), H1:S1)
'                            ^^^^^^
0
votes

To return the column header of the last cell with value, use the following: 

=IFERROR(INDEX(H1:S1,MAX(IF(LOOKUP(2,1/(H228:S228<>""),H228:S228)=H228:S228,COLUMN(H228:S228),0))),"")

Array Formula press Ctrl+Shift+Enter at the same time

Array formula in case you have duplicates
H1:S1 is the Header Row
H228:S228 the last row with data
Lookup will find the last cell with data
Index will return the corresponding header