https://bugs.documentfoundation.org/show_bug.cgi?id=100663
--- Comment #3 from GerardF <[email protected]> ---
(In reply to Prasad from comment #2)
> Firstly the 4th parameter is optional, so it should not be of significance.
Optional doesn't mean useless...
Of course it is this has a significance.
Help for VLOOKUP (same principle as for HLOOKUP :
"VLOOKUP
Vertical search with reference to adjacent cells to the right. This function
checks if a specific value is contained in the first column of an array. The
function then returns the value in the same row of the column named by Index.
***If the SortOrder parameter is omitted or set to TRUE or one, it is assumed
that the data is sorted in ascending order. In this case, if the exact
SearchCriterion is not found, the last value that is smaller than the criterion
will be returned.*** If SortOrder is set to FALSE or zero, an exact match must
be found, otherwise the error Error: Value Not Available will be the result.
Thus with a value of zero the data does not need to be sorted in ascending
order.
Syntax
=VLOOKUP(SearchCriterion; Array; Index; SortOrder)
SearchCriterion is the value searched for in the first column of the array.
Array is the reference, which is to comprise at least two columns.
Index is the number of the column in the array that contains the value to be
returned. The first column has the number 1.
***SortOrder is an optional parameter that indicates whether the first column
in the array is sorted in ascending order. Enter the Boolean value FALSE or
zero if the first column is not 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 between the
lowest and highest value of the sorted list. In unsorted lists, the search
value must be matched exactly. Otherwise the function will return this message:
Error: Value Not Available.
Handling of Empty Cells
Example
You want to enter the number of a dish on the menu in cell A1, and the name of
the dish is to appear as text in the neighboring cell (B1) immediately. The
Number to Name assignment is contained in the D1:E100 array. D1 contains 100,
E1 contains the name Vegetable Soup, and so forth, for 100 menu items. The
numbers in column D are sorted in ascending order; thus, the optional SortOrder
parameter is not necessary.
Enter the following formula in B1:
=VLOOKUP(A1;D1:E100;2)
As soon as you enter a number in A1 B1 will show the corresponding text
contained in the second column of reference D1:E100. Entering a nonexistent
number displays the text with the next number down. To prevent this, enter
FALSE as the last parameter in the formula so that an error message is
generated when a nonexistent number is entered."
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
[email protected]
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
