[Solved] LOOKUP function returns odd results

Discuss the spreadsheet application

[Solved] LOOKUP function returns odd results

Postby HelloW » Sat Jul 20, 2019 3:37 am

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
HelloW
 
Posts: 2
Joined: Sat Jul 20, 2019 3:16 am

Re: LOOKUP function returns odd results

Postby FJCC » Sat Jul 20, 2019 4:04 am

The lookup vector has to be sorted in ascending order which would be A, A-, A+, B, B-, B+ ....
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7376
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: LOOKUP function returns odd results

Postby HelloW » Sat Jul 20, 2019 5:02 am

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   Expand viewCollapse view
=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
HelloW
 
Posts: 2
Joined: Sat Jul 20, 2019 3:16 am

Re: LOOKUP function returns odd results

Postby Villeroy » Sat Jul 20, 2019 9:09 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 27 guests