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

Can someone please help with this problem?

Somethingwill have happened. Why don't you tell? Is it fun to have us stabbing in the dark?Did you

understandthe formula you used inExcel? Why did you expect it to work as described?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 elementif all cells in L2:R2 have contentbecause 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.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

doeswork 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)`

I created tdf#116216 for this.

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