Cells Java VLOOKUP with "approximate match TRUE" fails when both: a) range contains a single row and b) range doesn't start on 'A' column

Aspose.Cells Product Family
1

Attached is a test spreadsheet that shows the issue.

VLOOKUP fails in the special case where all of the following is true:

  • “approximate match” is set to TRUE
  • the vlookup range only contains a single row
  • the range starts somewhere other than in the first (i.e. ‘A’) column.

The spreadsheet we have been given to work with has this failing case in hundreds of places inside it.

Here’s a code sample (in Kotlin) that shows the issue happening:

        val worksheet = worksheets["Sheet1"]
        listOf(
            "just one vlookup true, false, A539:D539  -- passes ",
            worksheet.calculateFormula("=VLOOKUP(\"a\",A539:D539,2,TRUE)"),
            worksheet.calculateFormula("=VLOOKUP(\"a\",A539:D539,2,FALSE)"),

            "just one vlookup true, false, A1:D1  -- passes",
            worksheet.calculateFormula("=VLOOKUP(\"a\",A1:D1,2,TRUE)"),
            worksheet.calculateFormula("=VLOOKUP(\"a\",A1:D1,2,FALSE)"),

            "just one vlookup true, false, A7:D7  -- passes",
            worksheet.calculateFormula("=VLOOKUP(\"a\",A7:D7,2,TRUE)"),
            worksheet.calculateFormula("=VLOOKUP(\"a\",A7:D7,2,FALSE)"),

            "just one vlookup true, false, G7:J7  -- true fails",
            worksheet.calculateFormula("=VLOOKUP(\"a\",G7:J7,2,TRUE)"),
            worksheet.calculateFormula("=VLOOKUP(\"a\",G7:J7,2,FALSE)"),

            "just one vlookup true, false, G7:J8  -- true passes if range has more than one row",
            worksheet.calculateFormula("=VLOOKUP(\"a\",G7:J8,2,TRUE)"),
            worksheet.calculateFormula("=VLOOKUP(\"a\",G7:J8,2,FALSE)"),

            "just one vlookup true, false, B2:E2  -- true ",
            worksheet.calculateFormula("=VLOOKUP(\"a\",B2:E2,2,TRUE)"),
            worksheet.calculateFormula("=VLOOKUP(\"a\",B2:E2,2,FALSE)"),
        ).forEach { println(it) }

The above produces the output:

just one vlookup true, false, A539:D539  -- passes 
15000.0
15000.0
just one vlookup true, false, A1:D1  -- passes
15000.0
15000.0
just one vlookup true, false, A7:D7  -- passes
15000.0
15000.0
just one vlookup true, false, G7:J7  -- true fails
#N/A
15000.0
just one vlookup true, false, G7:J8  -- true passes if range has more than one row
15000.0
15000.0
just one vlookup true, false, B2:E2  -- true 
#N/A
15000.0

image.png (34.9 KB)

vlookup-single-row-problem.xlsx.zip (7.0 KB)

2

@twadzinsnea
By using sample code and files for testing, we can reproduce the issue. Discovered VLOOKUP function calculation returning incorrect value.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-45707

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

3

Thanks John!

We are in the process of renewing our expired subscription (I believe our purchasing people have been in contact with aspose over the last few days). Would having an active subscription escalate the priority of getting this fixed?

4

@twadzinsnea
We are already evaluating your issue and will notify you immediately if there are any updates. According to company regulations, only those with priority services will provide fix versions, so if you want to obtain fix versions faster, you need to purchase priority support. If there is no priority service, fix will also be released in the next release version.
You may check our paid support option where issues are prioritized and posses highest priority if your issue is a real blocker.

5

@twadzinsnea,

We are pleased to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose.Cells v23.12) that we plan to release in the second week of December next month. You will be notified when the new version is published.

1 Like
6

The issues you have found earlier (filed as CELLSJAVA-45707) have been fixed in Aspose.Cells for Java 23.12.