LOOKUP with nested ISTEXT/ISNUMBER/ISBLANK returns a different value than expected (compared to Excel)

[qnl2018]

If I have column A as
Test #1
7
[Blank]
Test #2
2
Test #2b
Test #3
5
Test #3b
Test #4

And in Column B, I have the formula : =IFERROR(LOOKUP(2, 1/(ISTEXT(A$1:A1)), A$1:A1), " ") with the B10 formula then being : =IFERROR(LOOKUP(2, 1/(ISTEXT(A$1:A10)), A$1:A10), " ")
I would expect the result to be
Test #1
Test #1
Test #1
Test #2
Test #2
Test #3
Test #3
Test #3b
Test #4

However what I get is
Test #1
Test #1
Test #1
Test #1
Test #1
Test #1
Test #2
Test #3
Test #1
Test #1

The formula works perfectly well in Excel but not in calc. What am I doing wrong? Is there a what to fix the formula? or Did I stumble upon a bug?

[qnl2018]

I was able to figure out a work around using array formulas using, INDIRECT, MAX, ISTEXT, and ROW functions.

The array formula in B10 looks like :

{=IFERROR(INDIRECT(“A”&MAX((ISTEXT(A$1:A10))*ROW(A$1:A10)))," ")}

The IFERROR is only used in the case where the first line doesn’t contain text. It works in LibreOffice Calc, Excel, and Google Sheets.

[mikekaganski]

Great!

[mikekaganski]

According to the documentation for LOOKUP spreadsheet function:

Additionally, the search vector for the LOOKUP must be sorted ascending, otherwise the search will not return any usable results.

This is no different for Excel. So you see the difference in the garbage returned for incorrect input by two different programs. This is not a bug.

[qnl2018]

So is there a way to fix the issue to return what is expected?

[Lupp]

Quoting @qnl2018: “So is there a way to fix the issue to return what is expected?”
Omit the usage of LOOKUP(). This function is too inflexible. Replace it with a combination of MATCH() and INDEX().