Bug 153312

Summary:	EDITING Data Validation/Vlookup pulldown list won't work unless items alphabetical
Product:	LibreOffice	Reporter:	Paul Kinzelman <paul>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	CLOSED NOTABUG
Severity:	normal	CC:	paul
Priority:	medium
Version:	7.4.4.2 release
Hardware:	All
OS:	Windows (All)
Whiteboard:
Crash report or crash signature:		Regression By:
Attachments:	Simple example file that illustrates the bug

Description Paul Kinzelman 2023-02-01 17:56:22 UTC

Description:
I created a simple example of a pulldown list consisting of a vertical list of items. If the items are not in alphabetical order, the pulldown does not work.

The pulldown can select an item that's not alphabetical, but the adjacent VLOOKUP function to have that selection put a value in another cell doesn't work.

I've attached a simple example to illustrate.
A5:A8 - list of pulldown items
B5:B8 - list of data I want when I select the corresponding A item
D4 is the pulldown to select
E4 contains the result of the pulldown data from column B.

If you pulldown and select FFF you get Ftoo.
If you pulldown and select DDD you get Dtoo.
If you pulldown and select BBB you get a blank (probably error)
If you pulldown and select AAA you get a blank

If I reorder the data in A5:B8 alphabetically, it works, I get the
-too data in E4 as expected.


Steps to Reproduce:
1.See description
2.
3.

Actual Results:
See description

Expected Results:
See description



Reproducible: Always


User Profile Reset: No

Additional Info:
Version: 7.4.4.2 (x64) / LibreOffice Community
Build ID: 85569322deea74ec9134968a29af2df5663baa21
CPU threads: 12; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

Comment 1 Paul Kinzelman 2023-02-01 17:58:30 UTC

Created attachment 185052 [details]
Simple example file that illustrates the bug

Comment 2 Paul Kinzelman 2023-02-01 18:15:51 UTC

I forgot to mention, when I set up the Data | Validity box, I selected:
"Cell range", then
Allow empty cells (I did some testing with these as well)
Show selection list

And I had OFF this one:
Sort entries ascending

Comment 3 Eike Rathke 2023-02-01 18:36:03 UTC

VLOOKUP() without 4th parameter or 4th parameter's argument being not 0 does a range lookup for which the lookup range MUST be sorted ascending. If you want an exact match instead specify 0 as 4th argument, like

=IFERROR(VLOOKUP(D4;A5:B8;2;0);" ")

See online help https://help.libreoffice.org/7.5/en-GB/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3153152

Btw, I'd not use IFERROR() but IFNA() instead to not suppress other than #N/A errors.

Not a bug.

Comment 4 Paul Kinzelman 2023-02-01 18:40:15 UTC

Sorry, thanks for the clear explanation