# 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?

edit retag 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?

( 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.

( 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)

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

I created tdf#116216 for this.

( 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.

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

Sort by » oldest newest most voted

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

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.

( 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!!!!!!

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