Free Support Forum - aspose.com

Formula error: VLOOKUP doesn't work since 4.0.3.4

Hi,
since version 4.0.3.4 the VLOOKUP function isn’t working any more. Our code produces the following formula:

=IF(ISERROR(VLOOKUP(A2;DB_Insert;3;TRUE));11688689320;VLOOKUP(A2;DB_Insert;3;TRUE))

But, the formula is always producing an error, and puts in the default value. If I copy the value from the cell (in excel) and paste it in another cell, the correct value gets calculated.

Pls find attached a sample sheet (wrong column c with the formula) and column i with the pasted code that works.

Am I doing sth wrong?
Any help appreciated, thanks

Michael

Hi Michael,

Thanks for considering Aspose.

I think the problem is with named ranges and when implementing VLOOKUP function the cells also gets converted to Number format too. We have to convert the cell into general format first.

You may currently try the following sample code it works fine I tested with your excel file:

(I applied your formula in E6 cell with your formula and regarding named range i just used the original cells range in the Sheet2)

Workbook workbook = new Workbook();

workbook.Open("d:\\AsposeError.xls",FileFormatType.Excel2003);

workbook.Worksheets[0].Cells["E6"].Style.Number = 0;

workbook.Worksheets[0].Cells["E6"].Formula = "=IF(ISERROR(VLOOKUP(A6,Sheet2!A1:D19,3,True)),11688689320,VLOOKUP(A6,Sheet2!A1:D19,3,True))";

workbook.Save("d:\\testing.xls",FileFormatType.Excel2003);

Regarding VLOOKUP named ranges issue , we will sort out soon.

Thanks

Regards

Amjad Sahi

Aspose Nanjing Team

Please try this attached version.

hey,
thank you very much. It’s working again.

Michael