Can someone please help with this problem?
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?
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.
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)
Thank you for reporting this error. It is very frustrating to work around it for the intended result.
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.
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!!!
If the answer solves your question please tick the 
.