Created attachment 125970 [details] File with hlookup returned values and expected values HLOOKUP returns some erratic result. It displays wrong values. Please have a look at the spreadsheet attached. It also returns a value for a name which is not present in the given array.
Have a look at the help for this function. You just have omitted the 4th parameter.
Firstly the 4th parameter is optional, so it should not be of significance. Secondly, the function is returning wrong values all over. Its returning a value for a search criterion which is not in the array. Kindly see the excel sheet attached.
(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."
As i have mentioned earlier, i have tried everything i.e. putting 0 in all empty spaces. I still dont get the desired value. I hv tried using the 4th parameter as well.
Created attachment 125994 [details] Test file with modified. Sometimes is good to earth what people with acknowledgement says. All works fine with the 4th parameter as cero. In F31 you don't get the expected value because you have not in F30 "Siddesh D" the same text than in H1 "Siddhesh D", there is an 'h' after 'dd' in H1. Attached your test file corrected. Resolved as not a bug.
Well i had tried putting the 4th parameter as 0 but it did not work. but today i recd an update notification of libre calc and after update things are working fine. thanks