[Solved] LOOKUP function returns odd results

Discuss the spreadsheet application
Post Reply
HelloW
Posts: 2
Joined: Sat Jul 20, 2019 3:16 am

[Solved] LOOKUP function returns odd results

Post by HelloW »

Hey,

what's wrong with my fornula?
I attached screenshot to the post.

Both columns should be identical... shouldn't they?
In case the picture isn't too clear, the right row contains the formula and I typed in values in the left one.

I have Enable regular expressions in formula checked and Search criteria = and <> must apply to whole cell unchecked. Anyway I tried all combinations of check/uncheck, nothing changed. I'm guessing it has to do with the + and - symbols, but I can't figure out how to fix it... Can you guys help me out?

Thanks a lot
Attachments
setings.png
formula.png
Last edited by HelloW on Sat Jul 20, 2019 3:12 pm, edited 2 times in total.
Openoffice: 6.0.7.3 on Ubuntu 0.18.04.5
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: LOOKUP function returns odd results

Post by FJCC »

The lookup vector has to be sorted in ascending order which would be A, A-, A+, B, B-, B+ ....
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
HelloW
Posts: 2
Joined: Sat Jul 20, 2019 3:16 am

Re: LOOKUP function returns odd results

Post by HelloW »

Ah! I see. That's probably what they mean in the doc by "searchtable must be sorted, with numbers in ascending order appearing before text values in alphabetic order. " -_-

And I also had to change my resulttable order, for it to fit the new order of the lookup vector.

Well, that was simple. And I'm a dummy, will learn to read next time. Thanks for the quick response!! :)

edit:
Here's the formula that worked for me:

Code: Select all

=LOOKUP(B4,{"A","A-","A+","B","B-","B+","C","C-","C+","D","D+","E"},{4,3.67,4.33,3,2.67,3.33,2,1.67,2.33,1,1.33,0})
A+ = 4.33, A = 4.00, A = 3.67, ... D = 1.00, E = 0.00.

Thanks for the words of advice Villeroy
Last edited by HelloW on Sat Jul 20, 2019 3:26 pm, edited 5 times in total.
Openoffice: 6.0.7.3 on Ubuntu 0.18.04.5
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: LOOKUP function returns odd results

Post by Villeroy »

Even with a sorted search vector you get wrong positive results for arbitrary text value. LOOKUP does not do what most people think it would do. It does not look up one distinct item from a list.
Another issue is Tools>Options>Calc>Calculation: "Enable regular expressions in formulas" which should be off, particularly when special characters ^$+.*?<>{}[]() are involved. Option "= and <> must apply to whole cells" should be on, particularly when you need to distinguish "Axx" from "Ax" and "A".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply