2

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?

Mike
  • 191
  • 1
  • 6
  • Check the documentation of [LOOKUP](https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-8251-369d5e3864cb), for the third input argument, it requires one column or one row. Maybe there are other ways to achieve that, do you have any excel version constraints? – David Leal Nov 04 '22 at 16:32
  • 1
    Note that the supplied link for LOOKUP is to Microsoft, I'm using OpenOffice Calc. F1:I1 is indeed just one row, not an array. I can confirm the third input isn't the problem because the function works correctly if I put dummy data in F4:I4. I'm not sure what you mean by excel version constraints. – Mike Nov 04 '22 at 16:49
  • I see @Mike I tested it with sample data and it worked for me the following: `=LOOKUP(2,1/(A3:B3<>""), A1:B1)` so it is the same formula you have applied to a different range. Have you tried only in Excel, maybe there is an issue with OpenOffice? – David Leal Nov 04 '22 at 16:56
  • I only have OpenOffice Calc to try it on. My question is why does OpenOffice give a #DIV/0 error when this same formula works in Excel? – Mike Nov 04 '22 at 17:15
  • I see, sorry then I cannot help, I don't have OpenOffice, I use Excel Web version – David Leal Nov 04 '22 at 17:16
  • The term OpenOffice should no longer be used for StarOffice derivatives due to old copyright issues. So it was renamed to OpenOffice.org, but that's now outdated as well. Are you asking about LibreOffice or Apache OpenOffice? The same answers often work for both, but still, you should specify which in the question in case it matters. If you fix this, then I'll upvote. – Jim K Nov 07 '22 at 17:29
  • 1
    Updated, I'm using Apache OpenOffice – Mike Nov 08 '22 at 18:31
  • Is there a reason you must use Apache OpenOffice? It's basically a very old version of LibreOffice. (This is sort of a rhetorical comment — you don't necessarily need to respond). – Jim K Nov 09 '22 at 13:32
  • The main reason is just momentum, it's what I use. I had hoped to adapt my tool to the job rather than have to get and learn a new tool. That said, this has taught me the AOO is finicky and doesn't behave similarly to excel or OpenOffice.org, so that may justify changing in the future. – Mike Nov 09 '22 at 18:32

2 Answers2

1

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:

  • Works with Apache OpenOffice
  • Works when there are empty cells in the row

=LOOKUP("ZZZ";F9:ZZ9;F1:ZZ1)

Many thanks to those that helped with this!

Mike
  • 191
  • 1
  • 6
0

Adapted from https://ask.libreoffice.org/t/return-the-last-value-in-a-column/53646:

=LOOKUP(2;1/ISNUMBER(H228:S228);H228:S228)

Here is the explanation from that post:

LOOKUP() forces its arguments into array mode. In this ISNUMBER(...) returns an array of 1 and 0 values, then 1/ISNUMBER(...) produces an array of 1 and #DIV/0 error values. LOOKUP() has the behaviour of silently ignoring error values in such array, thus looking up the value 2 returns the position of the last 1 value. Looking up 1 instead in this case works as well, but internally more elements are compared to find the last matching value, whereas for value 2 the search does a complete binary search to rule out blocks of data, which is faster.

EDIT:

This works for text as well as numbers.

=LOOKUP(2;1/NOT(ISBLANK(H228:S228));H228:S228)

EDIT 2:

Here is a solution for AOO, adapted from https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=66962:

=LOOKUP("ZZZ";H228:S228)
Jim K
  • 12,824
  • 2
  • 22
  • 51
  • 1
    Unfortunately in my case this just returns a #DIV/0 Error too, I think because in my case the cells contain text. If I enter numbers in all the cells it works, but f any cells are empty in the range it gives a #DIV/0 error. Very frustrating that the formula isn't handled the same as excel – Mike Nov 06 '22 at 22:41
  • In the reference forum post, one poster said "LOOKUP() has the behaviour of silently ignoring error values in such array, thus looking up the value 2 returns the position of the last 1 value." But that statement is nowhere in Libreoffice.org help. I think that poster was taking that statement from the Excel documentation of LOOKUP() – Mike Nov 06 '22 at 22:55
  • @Mike: I edited the answer to handle to your new requirement. – Jim K Nov 07 '22 at 17:09
  • Unfortunately =LOOKUP(2;1/NOT(ISBLANK(H228:S228));H228:S228) still gives a #DIV/0 error if there are any blank rows – Mike Nov 08 '22 at 18:33
  • See edited answer for a solution that works in AOO. – Jim K Nov 09 '22 at 13:05