I'm working in Apache OpenOffice calc (v4.1.13) and would like to search a row, find the last row with a non-empty cell and return the column header for that row. Note that the cells contain text and that some cells in the row are empty.
In this post they used =LOOKUP(2,1/(H228:S228<>""), H1:S1) to return the column header of the last row with data. I first changed the commas to semicolons to work with OpenOffice, then changed the row values to match my ranges to get the following: =LOOKUP(2;1/(F4:I4<>"");F1:I1) but I get a #DIV/0 error.
The #DIV/0 error goes away and the function works if I put dummy data in what were blank cells in F4:I4.
From what I understand, in Excel this formula will work if some of the cells in the row are empty. This seems not to be the case in OpenOffice Calc, as I get a #DIV/0 error. How do I make this work in Calc?
Jim K got very close, I've adapted his suggestion and post it here. The following formula finds the last column with text in row X (in this case 9) and returns row Y of that same column (in this case #1, column header). I can confirm the following:
=LOOKUP("ZZZ";F9:ZZ9;F1:ZZ1)
Many thanks to those that helped with this!