Ask Your Question

I am trying to match the last non blank cell in a row with the header above it. I used this formula in Excel, =LOOKUP(2,1/(L2:R2<>""),M$1:S$1) but its not working in LibreOffice. Any suggestions?

asked 2018-03-04 01:07:59 +0200

Markc2265 gravatar image

updated 2018-03-04 04:28:32 +0200

Can someone please help with this problem?

edit retag flag offensive close merge delete


Something will have happened. Why don't you tell? Is it fun to have us stabbing in the dark?
Did you understand the formula you used in Excel? Why did you expect it to work as described?

Lupp gravatar imageLupp ( 2018-03-04 14:16:16 +0200 )edit

Just some explanation what actually happens in that formula: LOOKUP forces its arguments into array context, so L2:R2<>"" returns an array of 1 or 0 values, 1 if a cell is not empty and 0 if a cell is empty. 1/L2:R2<>"" then the reciprocal value. A LOOKUP of 2 picks the last element if all cells in L2:R2 have content because there is no value of 2 and the position of last value <= query is matched. If a cell is empty the array contains a #DIV/0! error.

erAck gravatar imageerAck ( 2018-03-05 18:04:29 +0200 )edit

The sort order of errors is behind values and strings, and apparently this "error behind value in array case" is not handled correctly by LOOKUP. It does work if 1/L2:R2<>"" is entered as array on a row (for example in L3:R3) and then that cell range is used as in =LOOKUP(2;L3:R3;M$1:S$1)

erAck gravatar imageerAck ( 2018-03-05 18:17:11 +0200 )edit

I created tdf#116216 for this.

erAck gravatar imageerAck ( 2018-03-05 21:52:44 +0200 )edit

Thank you for reporting this error. It is very frustrating to work around it for the intended result.

Markc2265 gravatar imageMarkc2265 ( 2018-03-05 21:57:59 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-03-04 14:34:40 +0200

Lupp gravatar image

updated 2018-03-04 14:36:28 +0200

To find something suiting your needs, you may study this attached example.

edit flag offensive delete link more


A formula like =SUMPRODUCT(MAX(COLUMN($A$1:$Z$1)*NOT(ISBLANK($A$1:$Z$1)))) find the last not blank cell, and a formula like =SUMPRODUCT(MAX(COLUMN($A$1:$Z$1)*($A$1:$Z$1<>""))) the last not blank or with no empty string value cell.

m.a.riosv gravatar imagem.a.riosv ( 2018-03-05 00:08:07 +0200 )edit

Thank you so much Lupp!!!! I really really appreciate your help. That spreadsheet example was very well done and was exactly what I needed. Thank you!!!!!!

Markc2265 gravatar imageMarkc2265 ( 2018-03-05 06:32:12 +0200 )edit

If the answer solves your question please tick the ✔.

m.a.riosv gravatar imagem.a.riosv ( 2018-03-05 13:01:16 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-04 01:07:59 +0200

Seen: 1,286 times

Last updated: Mar 04 '18