Lookup returns NA

Discuss the spreadsheet application
Post Reply
ReddPine
Posts: 1
Joined: Mon Aug 02, 2021 12:58 pm

Lookup returns NA

Post by ReddPine »

Attached is a screenshot showing my exact formula. The relevant parts of the screenshot are circled in red. If you can't view the image, this is what's happening:

Formula: =LOOKUP(0, C23:C123, E23:E123)
Cells: C38 contains the number 0. E38 contains the number 15.
The result of the formula is "NA".

Am I doing something wrong, or did I find a bug? If I'm looking up 0, I should get 15. Why am I getting on result at all? And why is the result 100 if I look up an impossible number like 10?
Attachments
Screenshot
Screenshot
CPU threads: 16; OS: Windows 10.0 Build 19043; UI render: Skia/Vulkan; VCL: win
LibreOffice 7.1.5.2 (x64)
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Lookup returns NA

Post by robleyd »

The LO Help for Lookup states that "the search vector for the LOOKUP must be sorted ascending, otherwise the search will not return any usable results." As best I can tell from your image, C23:C123 does not meet this criterion.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lookup returns NA

Post by Villeroy »

Even if you had a sorted search vector, LOOKUP would be the wrong function. VLOOKUP with mode 1
 Edit: sorry, mode 0 
can do the job.
=VLOOKUP(0; C23:E123; 3; 0) return from 3rd column of C23:E123 where 0 is matched exactly in the 1st column. Last argument 0 treats the first column as a list rather than a scale.

[Tutorial] VLOOKUP questions and answers
Last edited by Villeroy on Wed Aug 04, 2021 2:01 pm, edited 2 times in total.
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lookup returns NA

Post by Villeroy »

0 A
2 B
4 C
6 D

=LOOKUP(1;A1:B4) => A although 1 is not in the list. 0,2,4,6 is meant to be a scale rather than a list.
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