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 .