Does Aspose support Reverse Vlookup with Array Formula?

Aspose.Cells Product Family
1

reverse vlookup.zip (10.7 KB)
Is Aspose Cells able to support reverse vlookup with Array formula, I’m trying to search text in column B and getting value from column A, by using array formula, =VLOOKUP(F1,IF({1,0},B1:B2,A1:A2),2,FALSE). It work fine in MS Excel but I’m getting value #Ref when save as pdf with Aspose Cells

2

@leolow95,

Thanks for the template file and details.

After an initial test, I observed the issue as you mentioned by using your template file and following sample code. I found an issue when calculating reverse VLookup array formula:
e.g
Sample code:

    Workbook workbook = new Workbook("e:\\test2\\reverse vlookup.xlsx"); 
    Worksheet sheet = workbook.Worksheets[0]; 
    MessageBox.Show(sheet.Cells["B4"].StringValue);//1 
    workbook.CalculateFormula(); 
    MessageBox.Show(sheet.Cells["B4"].StringValue);//#REF!

I have logged a ticket with an id “CELLSNET-46258” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

3

@leolow95,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

4

Hi @Amjad_Sahi,

Thanks for the quick fix, can i know propbably when the fix will be released?

5

@leolow95,

You can expect the fix in the next few days (3-5 days or so). Once the fix is available, we will share the Download link (for the fix) here.

6

@leolow95,

Please try our latest version/fix: Aspose.Cells for .NET v18.7.5:

Your issue should be fixed in it.

Let us know your feedback.

7

@Amjad_Sahi,

I’m using Aspose Cells for java, where can I get the java version for this fix?

8

@leolow95,

We also fixed the issue in Java version. Please try our latest version/fix (attached): Aspose.Cells for Java v18.7.5.

Let us know your feedback.
Aspose.Cells for Java v18.7.5.zip (6.3 MB)
e.g
Sample code:

Workbook workbook = new Workbook("e:\\test2\\reverse vlookup.xlsx"); 
	    Worksheet sheet = workbook.getWorksheets().get(0); 
	    System.out.println(sheet.getCells().get("B4").getStringValue());//1 
	    workbook.calculateFormula(); 
	    System.out.println(sheet.getCells().get("B4").getStringValue());//1
9

The issues you have found earlier (filed as CELLSNET-46258) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi