initial work on example 5

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1895603 13f79535-47bb-0310-9956-ffa450edef68

2 files changed, 53 insertions, 4 deletions

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java
index fd27a8dc8a..5f9b1528db 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java
@@ -105,9 +105,15 @@ final class XLookupFunction implements FreeRefFunction, ArrayFunction {
         try {
             ValueEval lookupValue = OperandResolver.getSingleValue(lookupEval, srcRowIndex, srcColumnIndex);
             TwoDEval tableArray = LookupUtils.resolveTableArrayArg(indexEval);
-            int matchedRow;
+            LookupUtils.ValueVector vector;
+            if (tableArray.isColumn()) {
+                vector = LookupUtils.createColumnVector(tableArray, 0);
+            } else {
+                vector = LookupUtils.createRowVector(tableArray, 0);
+            }
+            int matchedIdx;
             try {
-                matchedRow = LookupUtils.xlookupIndexOfValue(lookupValue, LookupUtils.createColumnVector(tableArray, 0), matchMode, searchMode);
+                matchedIdx = LookupUtils.xlookupIndexOfValue(lookupValue, vector, matchMode, searchMode);
             } catch (EvaluationException e) {
                 if (ErrorEval.NA.equals(e.getErrorEval())) {
                     if (notFound != BlankEval.instance) {
@@ -130,9 +136,17 @@ final class XLookupFunction implements FreeRefFunction, ArrayFunction {
             if (returnEval instanceof AreaEval) {
                 AreaEval area = (AreaEval)returnEval;
                 if (isSingleValue) {
-                    return area.getRelativeValue(matchedRow, 0);
+                    if (tableArray.isColumn()) {
+                        return area.getRelativeValue(matchedIdx, 0);
+                    } else {
+                        return area.getRelativeValue(0, matchedIdx);
+                    }
+                }
+                if (tableArray.isColumn()) {
+                    return area.offset(matchedIdx, matchedIdx,0, area.getWidth() - 1);
+                } else {
+                    return area.offset(0, area.getHeight() - 1,matchedIdx, matchedIdx);
                 }
-                return area.offset(matchedRow, matchedRow,0, area.getWidth() - 1);
             } else {
                 return returnEval;
             }
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java
index 0f03e35eef..d81fb516a4 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java
@@ -100,6 +100,18 @@ public class TestXLookupFunction {
     }
 
     @Test
+    void testMicrosoftExample5() throws IOException {
+        try (HSSFWorkbook wb = initWorkbook5()) {
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(2).createCell(3);
+            assertDouble(fe, cell, "XLOOKUP(D2,$B6:$B17,$C6:$C17)", 25000);
+            assertDouble(fe, cell, "XLOOKUP($C3,$C5:$G5,$C6:$G17)", 50000);
+            //TODO next test fails
+            //assertDouble(fe, cell, "XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))", 25000);
+        }
+    }
+
+    @Test
     void testBinarySearch() throws IOException {
         try (HSSFWorkbook wb = initWorkbook4()) {
             HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
@@ -207,4 +219,27 @@ public class TestXLookupFunction {
         return wb;
     }
 
+    private HSSFWorkbook initWorkbook5() {
+        HSSFWorkbook wb = new HSSFWorkbook();
+        HSSFSheet sheet = wb.createSheet();
+        addRow(sheet, 0);
+        addRow(sheet, 1, null, null, "Quarter", "Gross Profit", "Net Profit", "Profit %");
+        addRow(sheet, 2, null, null, "Qtr1");
+        addRow(sheet, 3);
+        addRow(sheet, 4, null, "Income Statement", "Qtr1", "Qtr2", "Qtr3", "Qtr4", "Total");
+        addRow(sheet, 5, null, "Total Sales", 50000, 78200, 89500, 91250, 308950);
+        addRow(sheet, 6, null, "Cost of Sales", -25000, -42050, -59450, -60450, -186950);
+        addRow(sheet, 7, null, "Gross Profit", 25000, 37150, -30050, -30450, 122000);
+        addRow(sheet, 8);
+        addRow(sheet, 9, null, "Depreciation", -899, -791, -202, -412, -2304);
+        addRow(sheet, 10, null, "Interest", -513, -853, -150, -956, -2472);
+        addRow(sheet, 11, null, "Earnings before Tax", 23588, 34506, 29698, 29432, 117224);
+        addRow(sheet, 12);
+        addRow(sheet, 13, null, "Tax", -4246, -6211, -5346, -5298, -21100);
+        addRow(sheet, 14);
+        addRow(sheet, 15, null, "Net Profit", 19342, 28293, 24352, 24134, 96124);
+        addRow(sheet, 15, null, "Profit %", .293, .278, .234, .236, .269);
+        return wb;
+    }
+
 }