Excel API Library for .NET - Sample Browser | Document Solutions | XLOOKUP function
[
フレーム]
The XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP.
//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
// Exact match
IWorksheet ws1 = workbook.Worksheets["Sheet1"];
ws1.Name = "Exact match";
ws1.Range["$B2ドル:$B3ドル"].Value = new object[,] {
{ "#1 - basic exact match"},
{ "=XLOOKUP(H5,B6:B10,D6:D10)"}
};
ws1.Range["$B5ドル:$E10ドル"].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["$G5ドル:$G6ドル"].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["$H5ドル"].Value = "Toy Story";
ws1.Range["$H6ドル"].Formula = "=XLOOKUP(H5,B6:B10,D6:D10)";
// basic approximate match
IWorksheet ws2 = workbook.Worksheets.Add();
ws2.Name = "Approximate match";
ws2.Range["$B2ドル:$B3ドル"].Value = new object[,] {
{ "#2 - basic approximate match"},
{ "=XLOOKUP(E6,B6:B10,C6:C10,,-1)"}
};
ws2.Range["$B5ドル:$C10ドル"].Value = new object[,] {
{ "Quantily", "Discount"},
{ 0d, 0d},
{ 10d, 0.05d},
{ 25d, 0.1d},
{ 50d, 0.2d},
{ 100d, 0.25d}
};
ws2.Range["$E5ドル:$E6ドル"].Value = new object[,] {
{ "Quantily"},
{ 28d}
};
ws2.Range["$F5ドル"].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["$F6ドル"].Formula = "=XLOOKUP(E6,B6:B10,C6:C10,,-1)";
// multiple values
IWorksheet ws3 = workbook.Worksheets.Add();
ws3.Name = "multiple values";
ws3.Range["$B2ドル:$B3ドル"].Value = new object[,] {
{ "#3 - multiple values"},
{ "=XLOOKUP(B6,B9:B16,C9:E16)"}
};
ws3.Range["$B5ドル:$B6ドル"].Value = new object[,] {
{ "ID"},
{ 841d}
};
ws3.Range["$C5ドル:$E5ドル"].Value = new object[,] {
{ "First", "Last", "Department"}
};
ws3.Range["$B8ドル:$E16ドル"].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["$C6ドル"].Formula2 = "=XLOOKUP(B6,B9:B16,C9:E16)";
// two-way lookup
IWorksheet ws4 = workbook.Worksheets.Add();
ws4.Name = "two-way lookup";
ws4.Range["$B2ドル:$B3ドル"].Value = new object[,] {
{ "#4 - two-way lookup"},
{ "=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))"}
};
ws4.Range["$C5ドル:$F10ドル"].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["$H5ドル:$H7ドル"].Value = new object[,] {
{ "Material"},
{ "Group"},
{ "Result"}
};
ws4.Range["$I5ドル:$I6ドル"].Value = new object[,] {
{ "Glass"},
{ "B"}
};
ws4.Range["$B6ドル:$B10ドル"].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["$I7ドル"].Formula2 = "=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))";
// Fallback value
IWorksheet ws5 = workbook.Worksheets.Add();
ws5.Name = "Fallback value";
ws5.Range["$B2ドル:$B3ドル"].Value = new object[,] {
{ "#5 - not found message"},
{ "=XLOOKUP(H4,B5:B9,E5:E9,\"Not found\")"}
};
ws5.Range["$B5ドル:$E10ドル"].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["$G5ドル:$G6ドル"].Value = new object[,] {
{ "Movie"},
{ "Rank"}
};
ws5.Range["$H5ドル"].Value = "Godzilla";
ws5.Range["$H6ドル"].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["$B2ドル:$B3ドル"].Value = new object[,] {
{ "#6 - array arguments"},
{ "=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})"}
};
ws6.Range["$B5ドル:$B8ドル"].Value = new object[,] {
{ "Years"},
{ "Sales"},
{ "Year"},
{ "Result"}
};
ws6.Range["$C5ドル:$C7ドル"].Value = new object[,] {
{ "{1996,1997,1999,1995,1992}"},
{ "{61,126,673,362,159}"},
{ 1997d}
};
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
IWorksheet ws7 = workbook.Worksheets.Add();
ws7.Name = "return reference";
ws7.Range["$B2ドル:$B3ドル"].Value = new object[,] {
{ "#7 - return reference"},
{ "=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))"}
};
ws7.Range["$B5ドル:$C6ドル"].Value = new object[,] {
{ "Start", "End"},
{ "Grape", "Banana"}
};
ws7.Range["$D5ドル"].Value = "Total";
ws7.Range["$B8ドル:$E13ドル"].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["$D6ドル"].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("$B2ドル:$B3ドル").Value = New Object(,) {
{"#1 - basic exact match"},
{"=XLOOKUP(H5,B6:B10,D6:D10)"}
}
ws1.Range("$B5ドル:$E10ドル").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("$G5ドル:$G6ドル").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("$B2ドル:$B3ドル").Value = New Object(,) {
{"#2 - basic approximate match"},
{"=XLOOKUP(E6,B6:B10,C6:C10,,-1)"}
}
ws2.Range("$B5ドル:$C10ドル").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("$E5ドル:$E6ドル").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("$B2ドル:$B3ドル").Value = New Object(,) {
{"#3 - multiple values"},
{"=XLOOKUP(B6,B9:B16,C9:E16)"}
}
ws3.Range("$B5ドル:$B6ドル").Value = New Object(,) {
{"ID"},
{841.0R}
}
ws3.Range("$C5ドル:$E5ドル").Value = New Object(,) {
{"First", "Last", "Department"}
}
ws3.Range("$B8ドル:$E16ドル").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("$B2ドル:$B3ドル").Value = New Object(,) {
{"#4 - two-way lookup"},
{"=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))"}
}
ws4.Range("$C5ドル:$F10ドル").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("$H5ドル:$H7ドル").Value = New Object(,) {
{"Material"},
{"Group"},
{"Result"}
}
ws4.Range("$I5ドル:$I6ドル").Value = New Object(,) {
{"Glass"},
{"B"}
}
ws4.Range("$B6ドル:$B10ドル").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("$B2ドル:$B3ドル").Value = New Object(,) {
{"#5 - not found message"},
{"=XLOOKUP(H4,B5:B9,E5:E9,""Not found"")"}
}
ws5.Range("$B5ドル:$E10ドル").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("$G5ドル:$G6ドル").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("$B2ドル:$B3ドル").Value = New Object(,) {
{"#6 - array arguments"},
{"=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})"}
}
ws6.Range("$B5ドル:$B8ドル").Value = New Object(,) {
{"Years"},
{"Sales"},
{"Year"},
{"Result"}
}
ws6.Range("$C5ドル:$C7ドル").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("$B2ドル:$B3ドル").Value = New Object(,) {
{"#7 - return reference"},
{"=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))"}
}
ws7.Range("$B5ドル:$C6ドル").Value = New Object(,) {
{"Start", "End"},
{"Grape", "Banana"}
}
ws7.Range!D5.Value = "Total"
ws7.Range("$B8ドル:$E13ドル").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")