// 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");