Calculate Vlookup formula by Aspose.Cells 6.0.0.2 for .NET not working

Aspose.Cells Product Family
1

Hello,

Using the attached workbook, after doing

workbook.CalculateFormula(true);

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 v6.0.0.2.

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.

Thanks,

Tom

2

Hi,


Thanks for the template files.

I have tested your scenario using your template file and found the issue.
See the comments with the sample code:

Sample code:
Workbook workbook = new Workbook(“e:\test2\VlookupTest.xls”);

MessageBox.Show(workbook.Worksheets[0].Cells[“F6”].StringValue);//3 - OK

workbook.CalculateFormula();

MessageBox.Show(workbook.Worksheets[0].Cells[“F6”].StringValue);//5 - NOT OK

I have logged your issue with an id: CELLSNET-29777. We will figure it out soon.

Thank you.
3

Hi,

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.

4

Hi,

We have fixed this issue.

Please download: Aspose.Cells for .NET v6.0.1.7

5

The issues you have found earlier (filed as 29777) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.