//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); // Exact match IWorksheet ws1 = workbook.Worksheets["Sheet1"]; ws1.Name = "Exact match"; ws1.Range["$B$2:$B$3"].Value = new object[,] { { "#1 - basic exact match"}, { "=XLOOKUP(H5,B6:B10,D6:D10)"} }; ws1.Range["$B$5:$E$10"].Value = 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.Range["$G$5:$G$6"].Value = new object[,] { { "Movie"}, { "Sales"} }; // Apply table style. ITable ws1Table = ws1.Tables.Add(ws1.Range["B5:E10"], true); ws1Table.ConvertToRange(); ws1.Range["G5"].Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196); ws1.Range["G6"].Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230); ws1.Range["G5:G6"].Font.Color = System.Drawing.Color.White; ws1.Range["B:B"].ColumnWidth = 14; ws1.Range["$H$5"].Value = "Toy Story"; ws1.Range["$H$6"].Formula = "=XLOOKUP(H5,B6:B10,D6:D10)"; // basic approximate match IWorksheet ws2 = workbook.Worksheets.Add(); ws2.Name = "Approximate match"; ws2.Range["$B$2:$B$3"].Value = new object[,] { { "#2 - basic approximate match"}, { "=XLOOKUP(E6,B6:B10,C6:C10,,-1)"} }; ws2.Range["$B$5:$C$10"].Value = new object[,] { { "Quantily", "Discount"}, { 0d, 0d}, { 10d, 0.05d}, { 25d, 0.1d}, { 50d, 0.2d}, { 100d, 0.25d} }; ws2.Range["$E$5:$E$6"].Value = new object[,] { { "Quantily"}, { 28d} }; ws2.Range["$F$5"].Value = "Result"; // Apply table style. ITable ws2Table1 = ws2.Tables.Add(ws2.Range["B5:C10"], true); ITable ws2Table2 = ws2.Tables.Add(ws2.Range["E5:F6"], true); ws2Table1.ConvertToRange(); ws2Table2.ConvertToRange(); ws2.Range["$F$6"].Formula = "=XLOOKUP(E6,B6:B10,C6:C10,,-1)"; // multiple values IWorksheet ws3 = workbook.Worksheets.Add(); ws3.Name = "multiple values"; ws3.Range["$B$2:$B$3"].Value = new object[,] { { "#3 - multiple values"}, { "=XLOOKUP(B6,B9:B16,C9:E16)"} }; ws3.Range["$B$5:$B$6"].Value = new object[,] { { "ID"}, { 841d} }; ws3.Range["$C$5:$E$5"].Value = new object[,] { { "First", "Last", "Department"} }; ws3.Range["$B$8:$E$16"].Value = 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.Tables.Add(ws3.Range["B5:E6"], true); ITable ws3Table2 = ws3.Tables.Add(ws3.Range["B8:E16"], true); ws3Table1.ConvertToRange(); ws3Table2.ConvertToRange(); ws3.Range["E:E"].ColumnWidth = 12; ws3.Range["$C$6"].Formula2 = "=XLOOKUP(B6,B9:B16,C9:E16)"; // two-way lookup IWorksheet ws4 = workbook.Worksheets.Add(); ws4.Name = "two-way lookup"; ws4.Range["$B$2:$B$3"].Value = new object[,] { { "#4 - two-way lookup"}, { "=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))"} }; ws4.Range["$C$5:$F$10"].Value = 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.Range["$H$5:$H$7"].Value = new object[,] { { "Material"}, { "Group"}, { "Result"} }; ws4.Range["$I$5:$I$6"].Value = new object[,] { { "Glass"}, { "B"} }; ws4.Range["$B$6:$B$10"].Value = new object[,] { { "Vinyl"}, { "Wood"}, { "Glass"}, { "Steel"}, { "Titanium"} }; // Apply table style. ITable ws4Table = ws4.Tables.Add(ws4.Range["B5:F10"], true); ws4Table.ConvertToRange(); ws4.Range["H5,H7"].Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196); ws4.Range["H6"].Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230); ws4.Range["H5:H7"].Font.Color = System.Drawing.Color.White; ws4.Range["B5"].ClearContents(); ws4.Range["$I$7"].Formula2 = "=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))"; // Fallback value IWorksheet ws5 = workbook.Worksheets.Add(); ws5.Name = "Fallback value"; ws5.Range["$B$2:$B$3"].Value = new object[,] { { "#5 - not found message"}, { "=XLOOKUP(H4,B5:B9,E5:E9,\"Not found\")"} }; ws5.Range["$B$5:$E$10"].Value = 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.Range["$G$5:$G$6"].Value = new object[,] { { "Movie"}, { "Rank"} }; ws5.Range["$H$5"].Value = "Godzilla"; ws5.Range["$H$6"].Formula2 = "=XLOOKUP(H4,B5:B9,E5:E9,\"Not found\")"; // Apply table style. ITable ws5Table = ws5.Tables.Add(ws5.Range["B5:E10"], true); ws5Table.ConvertToRange(); ws5.Range["G5"].Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196); ws5.Range["G6"].Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230); ws5.Range["G5:G6"].Font.Color = System.Drawing.Color.White; ws5.Range["B:B"].ColumnWidth = 14; // Array arguments IWorksheet ws6 = workbook.Worksheets.Add(); ws6.Name = "array arguments"; ws6.Range["$B$2:$B$3"].Value = new object[,] { { "#6 - array arguments"}, { "=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})"} }; ws6.Range["$B$5:$B$8"].Value = new object[,] { { "Years"}, { "Sales"}, { "Year"}, { "Result"} }; ws6.Range["$C$5:$C$7"].Value = new object[,] { { "{1996,1997,1999,1995,1992}"}, { "{61,126,673,362,159}"}, { 1997d} }; ws6.Range["$C$8"].Formula2 = "=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})"; ws6.Range["B5,B7"].Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196); ws6.Range["B6,B8"].Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230); ws6.Range["B5:B8"].Font.Color = System.Drawing.Color.White; // Return reference IWorksheet ws7 = workbook.Worksheets.Add(); ws7.Name = "return reference"; ws7.Range["$B$2:$B$3"].Value = new object[,] { { "#7 - return reference"}, { "=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))"} }; ws7.Range["$B$5:$C$6"].Value = new object[,] { { "Start", "End"}, { "Grape", "Banana"} }; ws7.Range["$D$5"].Value = "Total"; ws7.Range["$B$8:$E$13"].Value = 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.Tables.Add(ws7.Range["B5:D6"], true); ITable ws7Table2 = ws7.Tables.Add(ws7.Range["B8:E13"], true); ws7Table1.ConvertToRange(); ws7Table2.ConvertToRange(); ws7.Range["$D$6"].Formula2 = "=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 Dim workbook As New Workbook ' Exact match Dim ws1 As IWorksheet = workbook.Worksheets("Sheet1") ws1.Name = "Exact match" ws1.Range("$B$2:$B$3").Value = New Object(,) { {"#1 - basic exact match"}, {"=XLOOKUP(H5,B6:B10,D6:D10)"} } ws1.Range("$B$5:$E$10").Value = New Object(,) { {"Movie", "Year", "Rank", "Sales"}, {"Fargo", 1996.0R, 5.0R, 61.0R}, {"L.A. Confidential", 1997.0R, 4.0R, 126.0R}, {"The Sixth Sense", 1999.0R, 1.0R, 673.0R}, {"Toy Story", 1995.0R, 2.0R, 362.0R}, {"Unforgiven", 1992.0R, 3.0R, 159.0R} } ws1.Range("$G$5:$G$6").Value = New Object(,) { {"Movie"}, {"Rank"} } ' Apply table style. Dim ws1Table As ITable = ws1.Tables.Add(ws1.Range("B5:E10"), True) ws1Table.ConvertToRange() ws1.Range("G5").Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196) ws1.Range("G6").Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230) ws1.Range("G5:G6").Font.Color = System.Drawing.Color.White ws1.Range("B:B").ColumnWidth = 14 ws1.Range!H5.Value = "Toy Story" ws1.Range!H6.Formula = "=XLOOKUP(H5,B6:B10,D6:D10)" ' basic approximate match Dim ws2 As IWorksheet = workbook.Worksheets.Add() ws2.Name = "Approximate match" ws2.Range("$B$2:$B$3").Value = New Object(,) { {"#2 - basic approximate match"}, {"=XLOOKUP(E6,B6:B10,C6:C10,,-1)"} } ws2.Range("$B$5:$C$10").Value = New Object(,) { {"Quantily", "Discount"}, {0R, 0R}, {10.0R, 0.05R}, {25.0R, 0.1R}, {50.0R, 0.2R}, {100.0R, 0.25R} } ws2.Range("$E$5:$E$6").Value = New Object(,) { {"Quantily"}, {28.0R} } ' Apply table style. Dim ws2Table1 As ITable = ws2.Tables.Add(ws2.Range("B5:C10"), True) Dim ws2Table2 As ITable = ws2.Tables.Add(ws2.Range("E5:F6"), True) ws2Table1.ConvertToRange() ws2Table2.ConvertToRange() ws2.Range!F5.Value = "Result" ws2.Range!F6.Formula = "=XLOOKUP(E6,B6:B10,C6:C10,,-1)" ' multiple values Dim ws3 As IWorksheet = workbook.Worksheets.Add() ws3.Name = "multiple values" ws3.Range("$B$2:$B$3").Value = New Object(,) { {"#3 - multiple values"}, {"=XLOOKUP(B6,B9:B16,C9:E16)"} } ws3.Range("$B$5:$B$6").Value = New Object(,) { {"ID"}, {841.0R} } ws3.Range("$C$5:$E$5").Value = New Object(,) { {"First", "Last", "Department"} } ws3.Range("$B$8:$E$16").Value = New Object(,) { {"ID", "First", "Last", "Department"}, {610.0R, "Janet", "Farley", "Fulfillment"}, {798.0R, "Steven", "Batista", "Sales"}, {841.0R, "Evelyn", "Monet", "Fulfillment"}, {886.0R, "Marilyn", "Bradley", "Fulfillment"}, {622.0R, "Jonathan", "Adder", "Marketing"}, {601.0R, "Adrian", "Birt", "Engineering"}, {869.0R, "Julie", "Irons", "Sales"}, {867.0R, "Erica", "Tan", "Fulfillment"} } ' Apply table style. Dim ws3Table1 As ITable = ws3.Tables.Add(ws3.Range("B5:E6"), True) Dim ws3Table2 As ITable = ws3.Tables.Add(ws3.Range("B8:E16"), True) ws3Table1.ConvertToRange() ws3Table2.ConvertToRange() ws3.Range("E:E").ColumnWidth = 12 ws3.Range!C6.Formula2 = "=XLOOKUP(B6,B9:B16,C9:E16)" ' two-way lookup Dim ws4 As IWorksheet = workbook.Worksheets.Add() ws4.Name = "two-way lookup" ws4.Range("$B$2:$B$3").Value = New Object(,) { {"#4 - two-way lookup"}, {"=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))"} } ws4.Range("$C$5:$F$10").Value = New Object(,) { {"A", "B", "C", "D"}, {10.0R, 11.5R, 13.23R, 15.21R}, {12.0R, 13.8R, 15.87R, 18.25R}, {15.0R, 17.25R, 19.84R, 22.81R}, {18.0R, 20.7R, 23.81R, 27.38R}, {23.0R, 26.45R, 30.42R, 34.98R} } ws4.Range("$H$5:$H$7").Value = New Object(,) { {"Material"}, {"Group"}, {"Result"} } ws4.Range("$I$5:$I$6").Value = New Object(,) { {"Glass"}, {"B"} } ws4.Range("$B$6:$B$10").Value = New Object(,) { {"Vinyl"}, {"Wood"}, {"Glass"}, {"Steel"}, {"Titanium"} } ' Apply table style. Dim ws4Table As ITable = ws4.Tables.Add(ws4.Range("B5:F10"), True) ws4Table.ConvertToRange() ws4.Range("H5,H7").Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196) ws4.Range("H6").Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230) ws4.Range("H5:H7").Font.Color = System.Drawing.Color.White ws4.Range("B5").ClearContents() ws4.Range!I7.Formula2 = "=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))" ' Fallback value Dim ws5 As IWorksheet = workbook.Worksheets.Add() ws5.Name = "Fallback value" ws5.Range("$B$2:$B$3").Value = New Object(,) { {"#5 - not found message"}, {"=XLOOKUP(H4,B5:B9,E5:E9,""Not found"")"} } ws5.Range("$B$5:$E$10").Value = New Object(,) { {"Movie", "Year", "Rank", "Sales"}, {"Fargo", 1996.0R, 5.0R, 61.0R}, {"L.A. Confidential", 1997.0R, 4.0R, 126.0R}, {"The Sixth Sense", 1999.0R, 1.0R, 673.0R}, {"Toy Story", 1995.0R, 2.0R, 362.0R}, {"Unforgiven", 1992.0R, 3.0R, 159.0R} } ws5.Range("$G$5:$G$6").Value = New Object(,) { {"Movie"}, {"Sales"} } ' Apply table style. Dim ws5Table As ITable = ws5.Tables.Add(ws5.Range("B5:E10"), True) ws5Table.ConvertToRange() ws5.Range("G5").Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196) ws5.Range("G6").Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230) ws5.Range("G5:G6").Font.Color = System.Drawing.Color.White ws5.Range("B:B").ColumnWidth = 14 ws5.Range!H5.Value = "Godzilla" ws5.Range!H6.Formula2 = "=XLOOKUP(H4,B5:B9,E5:E9,""Not found"")" ' Array arguments Dim ws6 As IWorksheet = workbook.Worksheets.Add() ws6.Name = "array arguments" ws6.Range("$B$2:$B$3").Value = New Object(,) { {"#6 - array arguments"}, {"=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})"} } ws6.Range("$B$5:$B$8").Value = New Object(,) { {"Years"}, {"Sales"}, {"Year"}, {"Result"} } ws6.Range("$C$5:$C$7").Value = New Object(,) { {"{1996,1997,1999,1995,1992}"}, {"{61,126,673,362,159}"}, {1997.0R} } ws6.Range!C8.Formula2 = "=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})" ws6.Range("B5,B7").Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196) ws6.Range("B6,B8").Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230) ws6.Range("B5:B8").Font.Color = System.Drawing.Color.White ' Return reference Dim ws7 As IWorksheet = workbook.Worksheets.Add() ws7.Name = "return reference" ws7.Range("$B$2:$B$3").Value = New Object(,) { {"#7 - return reference"}, {"=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))"} } ws7.Range("$B$5:$C$6").Value = New Object(,) { {"Start", "End"}, {"Grape", "Banana"} } ws7.Range!D5.Value = "Total" ws7.Range("$B$8:$E$13").Value = New Object(,) { {"Product", "Qty", "Price", "Total"}, {"Apple", 23.0R, 0.52R, 11.9R}, {"Grape", 98.0R, 0.77R, 75.28R}, {"Pear", 75.0R, 0.24R, 18.16R}, {"Banana", 95.0R, 0.18R, 17.25R}, {"Cherry", 42.0R, 0.16R, 6.8R} } ' Apply table style. Dim ws7Table1 As ITable = ws7.Tables.Add(ws7.Range("B5:D6"), True) Dim ws7Table2 As ITable = ws7.Tables.Add(ws7.Range("B8:E13"), True) ws7Table1.ConvertToRange() ws7Table2.ConvertToRange() ws7.Range!D6.Formula2 = "=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))" ' save to an excel file workbook.Save("XLookupFunction.xlsx")