Using the attached workbook, after doing
I find that the cell result that I get from F6 is 5, not 3 as it should be.
Could you suggest why this might be? Any workaround would be brilliant
I am using Aspose.cells v220.127.116.11.
Changing the negative value in cell A7 to it’s absolute value 7445.253 fixes the issue, but this isn’t really a solution as this should work with negative values. I’ve attached this file as well for comparison.
Workbook workbook = new Workbook(“e:\test2\VlookupTest.xls”); MessageBox.Show(workbook.Worksheets.Cells[“F6”].StringValue);//3 - OK workbook.CalculateFormula(); MessageBox.Show(workbook.Worksheets.Cells[“F6”].StringValue);//5 - NOT OK
Please check document about VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) method:
If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
Important : If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.
For example: if you change A4 as 1259, the result of MS Excel is still 3. In fact the next largest value that is less than lookup_value should be 1259, so the result of the formula is 2.
We could not calculate the correct value because the values in the first column of table_array is not placed in ascending sort order,
Anyway, we will try to calculate the same result as MS Excel.
We have fixed this issue.
Please download: Aspose.Cells for .NET v18.104.22.168