At 10:18 19/02/2015 +0200, Yavor Marinov wrote:
Dear OO team,
There is no team here, in fact: just other users, like you.
Lately one of my superior got problems using the Calc part if your product.
So it's not "my" product.
They are using the function LOOKUP in rows in which the text is not sorted in ascending or descending order the exact LOOKUP function is not working.
The help text makes very clear that this is not supported: "... the search vector for the LOOKUP must be sorted in ascending order, otherwise the search will not return any usable results." In addition, the Calc Guide says "Use LOOKUP when: The search data is sorted in ascending order."
There is attached file with the exact situation.
To search unsorted lists, you need to use either HLOOKUP() or VLOOKUP() as appropriate.
However, if we try this in MS Office the problem is non existent and we could lineup all of the rows.
If you prefer Microsoft Office, I believe licences are available for purchase. But in any case, the Microsoft web site says about the Excel LOOKUP() function "*Important* The values in _lookup_vector_ must be placed in ascending order. [...] If you do not do so, *LOOKUP* may not give the correct value." - which seems to contradict what you claim.
Any help, or further information will be much appreciated
Either: o Sort your "Reference table" by column D Or: o Replace your formula =LOOKUP($B$3;$D$5:$D$15;$E$5:$E$15) with =VLOOKUP($B$3;$D$5:$E$15;2;0) I trust this helps. Brian Barker --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
