## [Solved] LOOKUP function returns odd results

### [Solved] LOOKUP function returns odd results

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
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

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
FJCC
Moderator

Posts: 7226
Joined: Sat Nov 08, 2008 8:08 pm

### Re: LOOKUP function returns odd results

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

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

Villeroy
Volunteer

Posts: 26977
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany