// Create a new workbook Workbook workbook = new Workbook(); // Exact match IWorksheet ws1 = workbook.getWorksheets().get("Sheet1"); ws1.setName("Exact match"); ws1.getRange("$B$2:$B$3") .setValue(new Object[][] { { "#1 - basic exact match" }, { "=XLOOKUP(H5,B6:B10,D6:D10)" } }); ws1.getRange("$B$5:$E$10") .setValue(new Object[][] { { "Movie", "Year", "Rank", "Sales" }, { "Fargo", 1996d, 5d, 61d }, { "L.A. Confidential", 1997d, 4d, 126d }, { "The Sixth Sense", 1999d, 1d, 673d }, { "Toy Story", 1995d, 2d, 362d }, { "Unforgiven", 1992d, 3d, 159d } }); ws1.getRange("$G$5:$G$6").setValue(new Object[][] { { "Movie" }, { "Rank" } }); // Apply table style. ITable ws1Table = ws1.getTables().add(ws1.getRange("B5:E10"),true); ws1Table.convertToRange(); ws1.getRange("B5:E5,G5").getInterior().setColor(Color.FromArgb(68,114,196)); ws1.getRange("G6").getInterior().setColor(Color.FromArgb(155,194,230)); ws1.getRange("B5:E5,G5:G6").getFont().setColor(Color.GetWhite()); ws1.getRange("B:B").setColumnWidth(14); ws1.getRange("$H$5").setValue("Toy Story"); ws1.getRange("$H$6").setFormula("=XLOOKUP(H5,B6:B10,D6:D10)"); // basic approximate match IWorksheet ws2 = workbook.getWorksheets().add(); ws2.setName("Approximate match"); ws2.getRange("$B$2:$B$3") .setValue(new Object[][] { { "#2 - basic approximate match" }, { "=XLOOKUP(E6,B6:B10,C6:C10,,-1)" } }); ws2.getRange("$B$5:$C$10").setValue(new Object[][] { { "Quantily", "Discount" }, { 0d, 0d }, { 10d, 0.05d }, { 25d, 0.1d }, { 50d, 0.2d }, { 100d, 0.25d } }); ws2.getRange("$E$5:$E$6").setValue(new Object[][] { { "Quantily" }, { 28d } }); ws2.getRange("$F$5").setValue("Result"); // Apply table style. ITable ws2Table1 = ws2.getTables().add(ws2.getRange("B5:C10"),true); ITable ws2Table2 = ws2.getTables().add(ws2.getRange("E5:F6"),true); ws2Table1.convertToRange(); ws2Table2.convertToRange(); ws2.getRange("$F$6").setFormula("=XLOOKUP(E6,B6:B10,C6:C10,,-1)"); // multiple values IWorksheet ws3 = workbook.getWorksheets().add(); ws3.setName("multiple values"); ws3.getRange("$B$2:$B$3") .setValue(new Object[][] { { "#3 - multiple values" }, { "=XLOOKUP(B6,B9:B16,C9:E16)" } }); ws3.getRange("$B$5:$B$6").setValue(new Object[][] { { "ID" }, { 841d } }); ws3.getRange("$C$5:$E$5").setValue(new Object[][] { { "First", "Last", "Department" } }); ws3.getRange("$B$8:$E$16") .setValue(new Object[][] { { "ID", "First", "Last", "Department" }, { 610d, "Janet", "Farley", "Fulfillment" }, { 798d, "Steven", "Batista", "Sales" }, { 841d, "Evelyn", "Monet", "Fulfillment" }, { 886d, "Marilyn", "Bradley", "Fulfillment" }, { 622d, "Jonathan", "Adder", "Marketing" }, { 601d, "Adrian", "Birt", "Engineering" }, { 869d, "Julie", "Irons", "Sales" }, { 867d, "Erica", "Tan", "Fulfillment" } }); // Apply table style. ITable ws3Table1 = ws3.getTables().add(ws3.getRange("B5:E6"),true); ITable ws3Table2 = ws3.getTables().add(ws3.getRange("B8:E16"),true); ws3Table1.convertToRange(); ws3Table2.convertToRange(); ws3.getRange("E:E").setColumnWidth(12); ws3.getRange("$C$6").setFormula2("=XLOOKUP(B6,B9:B16,C9:E16)"); // two-way lookup IWorksheet ws4 = workbook.getWorksheets().add(); ws4.setName("two-way lookup"); ws4.getRange("$B$2:$B$3").setValue( new Object[][] { { "#4 - two-way lookup" }, { "=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))" } }); ws4.getRange("$C$5:$F$10") .setValue(new Object[][] { { "A", "B", "C", "D" }, { 10d, 11.5d, 13.23d, 15.21d }, { 12d, 13.8d, 15.87d, 18.25d }, { 15d, 17.25d, 19.84d, 22.81d }, { 18d, 20.7d, 23.81d, 27.38d }, { 23d, 26.45d, 30.42d, 34.98d } }); ws4.getRange("$H$5:$H$7").setValue(new Object[][] { { "Material" }, { "Group" }, { "Result" } }); ws4.getRange("$I$5:$I$6").setValue(new Object[][] { { "Glass" }, { "B" } }); ws4.getRange("$B$6:$B$10") .setValue(new Object[][] { { "Vinyl" }, { "Wood" }, { "Glass" }, { "Steel" }, { "Titanium" } }); // Apply table style. ITable ws4Table1 = ws4.getTables().add(ws4.getRange("B5:E6"),true); ws4Table1.convertToRange(); ws4.getRange("H5,H7").getInterior().setColor(Color.FromArgb(68,114,196)); ws4.getRange("H6").getInterior().setColor(Color.FromArgb(155,194,230)); ws4.getRange("H5:H7").getFont().setColor(Color.GetWhite()); ws4.getRange("B5").clearContents(); ws4.getRange("$I$7").setFormula2("=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))"); // Fallback value IWorksheet ws5 = workbook.getWorksheets().add(); ws5.setName("Fallback value"); ws5.getRange("$B$2:$B$3").setValue( new Object[][] { { "#5 - not found message" }, { "=XLOOKUP(H4,B5:B9,E5:E9,\"Not found\")" } }); ws5.getRange("$B$5:$E$10") .setValue(new Object[][] { { "Movie", "Year", "Rank", "Sales" }, { "Fargo", 1996d, 5d, 61d }, { "L.A. Confidential", 1997d, 4d, 126d }, { "The Sixth Sense", 1999d, 1d, 673d }, { "Toy Story", 1995d, 2d, 362d }, { "Unforgiven", 1992d, 3d, 159d } }); ws5.getRange("$G$5:$G$6").setValue(new Object[][] { { "Movie" }, { "Sales" } }); ws5.getRange("$H$5").setValue("Godzilla"); // Apply table style. ITable ws5Table1 = ws5.getTables().add(ws5.getRange("B5:E10"),true); ws5Table1.convertToRange(); ws5.getRange("G5").getInterior().setColor(Color.FromArgb(68,114,196)); ws5.getRange("G6").getInterior().setColor(Color.FromArgb(155,194,230)); ws5.getRange("G5:G6").getFont().setColor(Color.GetWhite()); ws5.getRange("B:B").setColumnWidth(14); ws5.getRange("$H$6").setFormula2("=XLOOKUP(H4,B5:B9,E5:E9,\"Not found\")"); // Array arguments IWorksheet ws6 = workbook.getWorksheets().add(); ws6.setName("array arguments"); ws6.getRange("$B$2:$B$3").setValue(new Object[][] { { "#6 - array arguments" }, { "=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})" } }); ws6.getRange("$B$5:$B$8").setValue(new Object[][] { { "Years" }, { "Sales" }, { "Year" }, { "Result" } }); ws6.getRange("$C$5:$C$7") .setValue(new Object[][] { { "{1996,1997,1999,1995,1992}" }, { "{61,126,673,362,159}" }, { 1997d } }); ws6.getRange("$C$8").setFormula2("=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})"); ws6.getRange("B5,B7").getInterior().setColor(Color.FromArgb(68,114,196)); ws6.getRange("B6,B8").getInterior().setColor(Color.FromArgb(155,194,230)); ws6.getRange("B5:B8").getFont().setColor(Color.GetWhite()); // Return reference IWorksheet ws7 = workbook.getWorksheets().add(); ws7.setName("return reference"); ws7.getRange("$B$2:$B$3").setValue(new Object[][] { { "#7 - return reference" }, { "=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))" } }); ws7.getRange("$B$5:$C$6").setValue(new Object[][] { { "Start", "End" }, { "Grape", "Banana" } }); ws7.getRange("$D$5").setValue("Total"); ws7.getRange("$B$8:$E$13") .setValue(new Object[][] { { "Product", "Qty", "Price", "Total" }, { "Apple", 23d, 0.52d, 11.9d }, { "Grape", 98d, 0.77d, 75.28d }, { "Pear", 75d, 0.24d, 18.16d }, { "Banana", 95d, 0.18d, 17.25d }, { "Cherry", 42d, 0.16d, 6.8d } }); // Apply table style. ITable ws7Table1 = ws7.getTables().add(ws7.getRange("B5:D6"),true); ITable ws7Table2 = ws7.getTables().add(ws7.getRange("B8:E13"),true); ws7Table1.convertToRange(); ws7Table2.convertToRange(); ws7.getRange("$D$6").setFormula2("=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))"); // Save to an excel file workbook.save("XLookupFunction.xlsx");
// Create a new workbook var workbook = Workbook() // Exact match val ws1: IWorksheet = workbook.getWorksheets().get("Sheet1") ws1.setName("Exact match") ws1.getRange("\$B$2:\$B$3") .setValue(arrayOf(arrayOf("#1 - basic exact match"), arrayOf("=XLOOKUP(H5,B6:B10,D6:D10)"))) ws1.getRange("\$B$5:\$E$10") .setValue(arrayOf(arrayOf("Movie", "Year", "Rank", "Sales"), arrayOf("Fargo", 1996.0, 5.0, 61.0), arrayOf("L.A. Confidential", 1997.0, 4.0, 126.0), arrayOf("The Sixth Sense", 1999.0, 1.0, 673.0), arrayOf("Toy Story", 1995.0, 2.0, 362.0), arrayOf("Unforgiven", 1992.0, 3.0, 159.0))) ws1.getRange("\$G$5:\$G$6").setValue(arrayOf(arrayOf("Movie"), arrayOf("Rank"))) // Apply table style. val ws1Table: ITable = ws1.getTables().add(ws1.getRange("B5:E10"), true) ws1Table.convertToRange() ws1.getRange("B5:E5,G5").getInterior().setColor(Color.FromArgb(68, 114, 196)) ws1.getRange("G6").getInterior().setColor(Color.FromArgb(155, 194, 230)) ws1.getRange("B5:E5,G5:G6").getFont().setColor(Color.GetWhite()) ws1.getRange("B:B").setColumnWidth(14.0) ws1.getRange("\$H$5").setValue("Toy Story") ws1.getRange("\$H$6").setFormula("=XLOOKUP(H5,B6:B10,D6:D10)") // basic approximate match val ws2: IWorksheet = workbook.getWorksheets().add() ws2.setName("Approximate match") ws2.getRange("\$B$2:\$B$3") .setValue(arrayOf(arrayOf("#2 - basic approximate match"), arrayOf("=XLOOKUP(E6,B6:B10,C6:C10,,-1)"))) ws2.getRange("\$B$5:\$C$10").setValue(arrayOf(arrayOf("Quantily", "Discount"), arrayOf(0.0, 0.0), arrayOf(10.0, 0.05), arrayOf(25.0, 0.1), arrayOf(50.0, 0.2), arrayOf(100.0, 0.25))) ws2.getRange("\$E$5:\$E$6").setValue(arrayOf(arrayOf("Quantily"), arrayOf(28.0))) ws2.getRange("\$F$5").setValue("Result") // Apply table style. val ws2Table1: ITable = ws2.getTables().add(ws2.getRange("B5:C10"), true) val ws2Table2: ITable = ws2.getTables().add(ws2.getRange("E5:F6"), true) ws2Table1.convertToRange() ws2Table2.convertToRange() ws2.getRange("\$F$6").setFormula("=XLOOKUP(E6,B6:B10,C6:C10,,-1)") // multiple values val ws3: IWorksheet = workbook.getWorksheets().add() ws3.setName("multiple values") ws3.getRange("\$B$2:\$B$3") .setValue(arrayOf(arrayOf("#3 - multiple values"), arrayOf("=XLOOKUP(B6,B9:B16,C9:E16)"))) ws3.getRange("\$B$5:\$B$6").setValue(arrayOf(arrayOf("ID"), arrayOf(841.0))) ws3.getRange("\$C$5:\$E$5").setValue(arrayOf(arrayOf("First", "Last", "Department"))) ws3.getRange("\$B$8:\$E$16") .setValue(arrayOf(arrayOf("ID", "First", "Last", "Department"), arrayOf(610.0, "Janet", "Farley", "Fulfillment"), arrayOf(798.0, "Steven", "Batista", "Sales"), arrayOf(841.0, "Evelyn", "Monet", "Fulfillment"), arrayOf(886.0, "Marilyn", "Bradley", "Fulfillment"), arrayOf(622.0, "Jonathan", "Adder", "Marketing"), arrayOf(601.0, "Adrian", "Birt", "Engineering"), arrayOf(869.0, "Julie", "Irons", "Sales"), arrayOf(867.0, "Erica", "Tan", "Fulfillment"))) // Apply table style. val ws3Table1: ITable = ws3.getTables().add(ws3.getRange("B5:E6"), true) val ws3Table2: ITable = ws3.getTables().add(ws3.getRange("B8:E16"), true) ws3Table1.convertToRange() ws3Table2.convertToRange() ws3.getRange("E:E").setColumnWidth(12.0) ws3.getRange("\$C$6").setFormula2("=XLOOKUP(B6,B9:B16,C9:E16)") // two-way lookup val ws4: IWorksheet = workbook.getWorksheets().add() ws4.setName("two-way lookup") ws4.getRange("\$B$2:\$B$3").setValue(arrayOf(arrayOf("#4 - two-way lookup"), arrayOf("=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))"))) ws4.getRange("\$C$5:\$F$10") .setValue(arrayOf(arrayOf("A", "B", "C", "D"), arrayOf(10.0, 11.5, 13.23, 15.21), arrayOf(12.0, 13.8, 15.87, 18.25), arrayOf(15.0, 17.25, 19.84, 22.81), arrayOf(18.0, 20.7, 23.81, 27.38), arrayOf(23.0, 26.45, 30.42, 34.98))) ws4.getRange("\$H$5:\$H$7").setValue(arrayOf(arrayOf("Material"), arrayOf("Group"), arrayOf("Result"))) ws4.getRange("\$I$5:\$I$6").setValue(arrayOf(arrayOf("Glass"), arrayOf("B"))) ws4.getRange("\$B$6:\$B$10") .setValue(arrayOf(arrayOf("Vinyl"), arrayOf("Wood"), arrayOf("Glass"), arrayOf("Steel"), arrayOf("Titanium"))) // Apply table style. val ws4Table1: ITable = ws4.getTables().add(ws4.getRange("B5:E6"), true) ws4Table1.convertToRange() ws4.getRange("H5,H7").getInterior().setColor(Color.FromArgb(68, 114, 196)) ws4.getRange("H6").getInterior().setColor(Color.FromArgb(155, 194, 230)) ws4.getRange("H5:H7").getFont().setColor(Color.GetWhite()) ws4.getRange("B5").clearContents() ws4.getRange("\$I$7").setFormula2("=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))") // Fallback value val ws5: IWorksheet = workbook.getWorksheets().add() ws5.setName("Fallback value") ws5.getRange("\$B$2:\$B$3").setValue(arrayOf(arrayOf("#5 - not found message"), arrayOf("=XLOOKUP(H4,B5:B9,E5:E9,\"Not found\")"))) ws5.getRange("\$B$5:\$E$10") .setValue(arrayOf(arrayOf("Movie", "Year", "Rank", "Sales"), arrayOf("Fargo", 1996.0, 5.0, 61.0), arrayOf("L.A. Confidential", 1997.0, 4.0, 126.0), arrayOf("The Sixth Sense", 1999.0, 1.0, 673.0), arrayOf("Toy Story", 1995.0, 2.0, 362.0), arrayOf("Unforgiven", 1992.0, 3.0, 159.0))) ws5.getRange("\$G$5:\$G$6").setValue(arrayOf(arrayOf("Movie"), arrayOf("Sales"))) ws5.getRange("\$H$5").setValue("Godzilla") // Apply table style. val ws5Table1: ITable = ws5.getTables().add(ws5.getRange("B5:E10"), true) ws5Table1.convertToRange() ws5.getRange("G5").getInterior().setColor(Color.FromArgb(68, 114, 196)) ws5.getRange("G6").getInterior().setColor(Color.FromArgb(155, 194, 230)) ws5.getRange("G5:G6").getFont().setColor(Color.GetWhite()) ws5.getRange("B:B").setColumnWidth(14.0) ws5.getRange("\$H$6").setFormula2("=XLOOKUP(H4,B5:B9,E5:E9,\"Not found\")") // Array arguments val ws6: IWorksheet = workbook.getWorksheets().add() ws6.setName("array arguments") ws6.getRange("\$B$2:\$B$3").setValue(arrayOf(arrayOf("#6 - array arguments"), arrayOf("=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})"))) ws6.getRange("\$B$5:\$B$8").setValue(arrayOf(arrayOf("Years"), arrayOf("Sales"), arrayOf("Year"), arrayOf("Result"))) ws6.getRange("\$C$5:\$C$7") .setValue(arrayOf(arrayOf("{1996,1997,1999,1995,1992}"), arrayOf("{61,126,673,362,159}"), arrayOf(1997.0))) ws6.getRange("\$C$8").setFormula2("=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})") ws6.getRange("B5,B7").getInterior().setColor(Color.FromArgb(68, 114, 196)) ws6.getRange("B6,B8").getInterior().setColor(Color.FromArgb(155, 194, 230)) ws6.getRange("B5:B8").getFont().setColor(Color.GetWhite()) // Return reference val ws7: IWorksheet = workbook.getWorksheets().add() ws7.setName("return reference") ws7.getRange("\$B$2:\$B$3").setValue(arrayOf(arrayOf("#7 - return reference"), arrayOf("=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))"))) ws7.getRange("\$B$5:\$C$6").setValue(arrayOf(arrayOf("Start", "End"), arrayOf("Grape", "Banana"))) ws7.getRange("\$D$5").setValue("Total") ws7.getRange("\$B$8:\$E$13") .setValue(arrayOf(arrayOf("Product", "Qty", "Price", "Total"), arrayOf("Apple", 23.0, 0.52, 11.9), arrayOf("Grape", 98.0, 0.77, 75.28), arrayOf("Pear", 75.0, 0.24, 18.16), arrayOf("Banana", 95.0, 0.18, 17.25), arrayOf("Cherry", 42.0, 0.16, 6.8))) // Apply table style. val ws7Table1: ITable = ws7.getTables().add(ws7.getRange("B5:D6"), true) val ws7Table2: ITable = ws7.getTables().add(ws7.getRange("B8:E13"), true) ws7Table1.convertToRange() ws7Table2.convertToRange() ws7.getRange("\$D$6").setFormula2("=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))") // Save to an excel file workbook.save("XLookupFunction.xlsx")