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?
Lookup returns NA
Lookup returns NA
CPU threads: 16; OS: Windows 10.0 Build 19043; UI render: Skia/Vulkan; VCL: win
LibreOffice 7.1.5.2 (x64)
LibreOffice 7.1.5.2 (x64)
Re: Lookup returns NA
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: Lookup returns NA
Even if you had a sorted search vector, LOOKUP would be the wrong function. VLOOKUP with mode 1
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
Edit: sorry, mode 0 |
=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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Lookup returns NA
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice