VLOOKUP not giving back correct values

[Laniakea]

Hello!

The VLOOKUP feature is giving me incorrect values.

I have two files. File A List.ods is where the VLOOKUP is.

The data is coming from File B Prices.ods

Now I want to be able to select an item from the drop down list in File A and then receive the corresponding data from File B.

As you can see it isn’t working correctly. For example: The item beer is supposed to be categorized as “beverage” yet VLOOKUP falsely declares it a fruit and also gives the wrong unit and price.

How do I fix this from happening?

Kind Regards
Laniakea

P.S.: My OS is Ubuntu Linux 64-bit & I’m running Version: 6.2.8.2 Build ID: 1:6.2.8~rc2-0ubuntu0.16.04.1

[anon73440385]

@Laniakea

There is no need to prepend your title by “[Solved]” or the like. Accepting an answer turns the check mark to green color and this way indicates that the problem has been solved / the answer is correct.

[anon73440385]

Hello,

LibreOffice Help - VLOOKUP states

SortedRangeLookup is an optional parameter that indicates whether the first column in the array contains range boundaries instead of plain values. In this mode, the lookup returns the value in the row with first column having value equal to or less than SearchCriterion. E.g., it could contain dates when some tax value had been changed, and so the values represent starting dates of a period when a specific tax value was effective. Thus, searching for a date that is absent in the first array column, but falls between some existing boundary dates, would give the lower of them, allowing to find out the data being effective to the searched date. Enter the Boolean value FALSE or zero if the first column is not a range boundary list. When this parameter is TRUE or not given, the first column in the array must be sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is greater than the lowest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return #N/A with message: Error: Value Not Available

and your Prices.ods is not sorted in column labeled Name. Hence you need to change your VLOOKUP() to use the fourth parameter 0 and show as (first line of your Lists.ods table):

=IF($B4="";"Select Item";(VLOOKUP($B4;'file:///home/uwe/Privat/Computer/Beratung/LibreOffice/TestingFiles/Calc/Prices.ods'#$Pricelist.$C$4:$F$7;2;0)))

=IF($B4="";"Select Item";(VLOOKUP($B4;'file:///home/uwe/Privat/Computer/Beratung/LibreOffice/TestingFiles/Calc/Prices.ods'#$Pricelist.$C$4:$F$7;3;0)))

=IF($B4="";"Select Item";(VLOOKUP($B4;'file:///home/uwe/Privat/Computer/Beratung/LibreOffice/TestingFiles/Calc/Prices.ods'#$Pricelist.$C$4:$F$7;4;0)))

Hope that helps.

[Laniakea]

Brilliant! That worked.
Also simply sorting the column “Name” first helped too. Then you don’t need the ;0 at the end.

Thank you so much!

[anon73440385]

Then you don’t need the ;0 at the end.

That’s true and and is more performant, however: Once forgotten to sort in the future after having added new prices, you may get unreliable (unnoticed) results. So my own preference is: Always use 0 unless there are performance reasons to force usage of sorted list.