Page 1 of 1

[Solved] LOOKUP function returns odd results

PostPosted: Sat Jul 20, 2019 3:37 am
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

Re: LOOKUP function returns odd results

PostPosted: Sat Jul 20, 2019 4:04 am
by FJCC
The lookup vector has to be sorted in ascending order which would be A, A-, A+, B, B-, B+ ....

Re: LOOKUP function returns odd results

PostPosted: Sat Jul 20, 2019 5:02 am
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   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

Re: LOOKUP function returns odd results

PostPosted: Sat Jul 20, 2019 9:09 am
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".