Bug 62275 - vlookup function with "empty" fourth argument can not be processed.
Summary: vlookup function with "empty" fourth argument can not be processed.
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.17-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-04-10 09:09 UTC by dolphin.in.the.sky.51
Modified: 2019-01-06 18:35 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description dolphin.in.the.sky.51 2018-04-10 09:09:12 UTC
vlookup function with "empty" fourth argument.
ex. vlookup(A1,E1:F10,2,)


An exception is raised.
Unexpected eval type (org.apache.poi.ss.formula.eval.MissingArgEval)


Sorry for bad explanation.
Comment 1 Taiki Sugawara 2018-07-05 13:48:38 UTC
I also encountered this bug.

I think it will be fixed that if we check the last argument is MissingArgEval in following classes:

- Var1or2ArgFunction
- Var2or3ArgFunction
- Var3or4ArgFunction

What do you think?
Comment 2 PJ Fanning 2018-07-05 14:07:00 UTC
Can you check with the latest nightly build?
https://poi.apache.org/download.html#nightly

I think that we might have added a fix for this already.
Comment 3 dolphin.in.the.sky.51 2018-07-27 08:06:28 UTC
This bug is not fixed.

Even in the latest nightly build, it is reproduced with the following code.

CreationHelper crateHelper = wb.getCreationHelper();
FormulaEvaluator eval = crateHelper.createFormulaEvaluator();
Cell cell = row.getCell(columnIndex);
eval.evaluate(cell);
Comment 4 PJ Fanning 2018-07-27 11:21:32 UTC
Could you provide a full test case?
Comment 5 dolphin.in.the.sky.51 2018-07-27 15:27:57 UTC
It reproduced with the following test case.

	public void test62275_bug() throws IOException {
	    Workbook wb = new XSSFWorkbook();
	    Sheet sheet = wb.createSheet();
	    Row row = sheet.createRow(0);
	    
	    Cell cell = row.createCell(0);
	    cell.setCellFormula("vlookup(A2,B1:B5,2,)");

	    CreationHelper crateHelper = wb.getCreationHelper();
	    FormulaEvaluator eval = crateHelper.createFormulaEvaluator();
	    eval.evaluate(cell);

	    wb.close();
	}
Comment 6 PJ Fanning 2018-07-28 07:12:47 UTC
https://svn.apache.org/viewvc?view=revision&revision=1836857 was merged
Comment 7 dolphin.in.the.sky.51 2018-07-30 01:12:00 UTC
Thank you for fixing.
but,In EXCEL, when the fourth argument of the vlookup function is "empty", it operates in the same way as when FALSE is specified.
I suggest adding the following to resolveRangeLookupArg method of LookupUtils.

if (valEval instanceof MissingArgEval) {
    return false;
}

Thank you.
Comment 8 Dominik Stadler 2018-12-27 21:05:58 UTC
This was actually already fixed some time ago via r1836857, the given test-case and some other cases work fine now.
Comment 9 Javen O'Neal 2019-01-04 19:27:55 UTC
(In reply to dolphin.in.the.sky.51 from comment #7)
> In EXCEL, when the fourth argument of the vlookup function is "empty",
> it operates in the same way as when FALSE is specified.

https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
> Optionally, you can specify TRUE if you want an approximate 
> match or FALSE if you want an exact match of the return 
> value. If you don't specify anything, the default value will 
> always be TRUE or approximate match.

In reply to Dominik Stadler from comment #8)
> This was actually already fixed some time ago via r1836857, the given
> test-case and some other cases work fine now.

>   try {
> +     isRangeLookup = LookupUtils.resolveRangeLookupArg(range_lookup, srcRowIndex, srcColumnIndex);
> + } catch(RuntimeException e) {
> +     isRangeLookup = true;
> + }

It appears that POI defaults the fourth argument to true if it's omitted.
Comment 10 Dominik Stadler 2019-01-06 18:35:39 UTC
At least the documentation at https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1 indicates that the default is "true", was the statement about Excel using "false" based on actual experiments?