[Solved] LOOKUP in LO Calc

[spriha]

This post was automatically translated into English.
Hello everyone,
I have the following problem with -REFERENCE-.
I would like to use LOOKUP to transfer a number from a sorted list to another list. A name is entered in the second list that is not present in the first list.
That's why there should be a zero or an empty cell for this name in the second list.
See attached file.
The letter F is missing in the table (A1:B12) = List 1. From here the reference to column E should be transferred to the names in column D. The letter F appears in the second list.
There should be a zero or an empty cell in the cell (column E).
I hope that someone can help me with this.

Appendix is ​​included.

Thank you spriha

[karolus]

Hallo
Start in E1 with:

Code: Select all

=WENNNV(SVERWEIS(D1;$A$1:$B$13;2;0);0)
# en
=IFNA(VLOOKUP(D1;$A$1:$B$13;2;0);0)

and pull down

[Villeroy]

LOOKUP in LO Calc

It's the same LOOKUP function since the very first version of Visicalc in the year 1979 and successive spreadsheet applications such as Lotus 1-2-3, Excel and Calc. LOOKUP never did what most users expect it to do.
In your specific example =CODE(D1)-43 returns a correct result without any lookup table.

[spriha]

Thanks for the quick help

spriha