Issue with VLOOKIP function

Aspose.Cells Product Family
1

Hi,

VLOOKUP does not work for exact match if “~” (Char(126)) is in the string. Excel will return “GOOD” while Aspose Cells Java will return “#NA”.

  1. Open blank worksheet, put “abc” in A1, “GOOD” in B1, “=VLOOKUP(”~abc",A:B,2,FALSE)" in C1.
  2. Calculate C1. Excel will show “GOOD” while Aspose will show “#NA”. Below the code piece,
    Workbook wb = new Workbook();
    Worksheet ws = wb.getWorksheets().get(0);
    Object result;
    Cell c = ws.getCells().get(0,0);
    c.setValue(“abc”);
    c = ws.getCells().get(0,1);
    c.setValue(“Good”);
    c = ws.getCells().get(0,2);
    c.setFormula("=VLOOKUP("~abc",A:B,2,FALSE)");
    c.calculate(new CalculationOptions());
    String str = c.getStringValue();
    System.out.println(str);
2

@huichen,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as

CELLSJAVA-42910 - Issue with VLOOKUP function when ~character is there in the string
3

@huichen,
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-42910”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

4

The issues you have found earlier (filed as CELLSJAVA-42910) have been fixed in Aspose.Cells for Java 19.5. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi

5

Hi,

I confirmed this issue has been fixed in v19.5. Thank you very much for the kind help and quick fix!

Regards,
Hui Chen

6

@huichen,
Good to know that your issue is sorted out by the suggested version. Feel free to contact us at any time if you need further help or have some other issue or queries, we will be happy to assist you soon.