Syntax
Argument
Description
lookup_value
(Required) The lookup value.
lookup_array
(Required) The array or range to search.
[match_mode]
(Optional) Specify the match type:0 - Exact match. If none found, return #N/A. This is the default.-1 - Exact match. If none found, return the next smaller item.1 - Exact match. If none found, return the next larger item.2 - A wildcard match where *, ?, and ~ have special meaning..
[search_mode]
(Optional) Specify the search mode to use:1 - Perform a search starting at the first item. This is the default.-1 - Perform a reverse search starting at the last item.2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.-2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
Usage notes
The Excel XMATCH function performs a lookup and returns a position. XMATCH can perform lookups in vertical or horizontal ranges, and is meant to be a more flexible and powerful successor to the MATCH function. XLOOKUP supports both approximate and exact matches, and wildcards (* ?) for partial matches. Like the XLOOKUP function, XMATCH can search data starting from the first value or the last value (i.e. reverse search). Finally, XMATCH can perform binary searches, which are specifically optimized for speed.
Policy
XMATCH can work with both vertical and horizontal arrays.
XMATCH will return #N/A if the lookup value is not found.
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss"));
spread.options.allowDynamicArray = true;
initStyles(spread);
initSpread(spread);
};
function initSpread(spread) {
spread.setSheetCount(4);
spread.suspendPaint();
spread.suspendCalcService();
initSheet1(spread.getSheet(0));
initSheet2(spread.getSheet(1));
initSheet3(spread.getSheet(2));
initSheet4(spread.getSheet(3));
spread.resumeCalcService();
spread.resumePaint();
}
function initStyles(spread) {
var introStyle = new GC.Spread.Sheets.Style();
introStyle.name = 'intro';
introStyle.font = 'normal bold 16px Segoe UI';
introStyle.foreColor = "#172b4d";
spread.addNamedStyle(introStyle);
var introStyle1 = new GC.Spread.Sheets.Style();
introStyle1.name = 'intro1';
introStyle1.font = 'normal bold 14px Calibri';
introStyle1.hAlign = 0;
introStyle1.vAlign = 1;
introStyle1.foreColor = "#172b4d";
spread.addNamedStyle(introStyle1);
var formulaStyle = new GC.Spread.Sheets.Style();
formulaStyle.name = 'formula';
formulaStyle.font = 'normal bold 12px Consolas';
formulaStyle.foreColor = "#c00000";
introStyle1.vAlign = 1;
spread.addNamedStyle(formulaStyle);
var tableHeaderStyle = new GC.Spread.Sheets.Style();
tableHeaderStyle.name = 'tableHeader';
tableHeaderStyle.font = "normal bold 14.7px Calibri";
tableHeaderStyle.hAlign = 1;
tableHeaderStyle.backColor = "#d9e1f2";
spread.addNamedStyle(tableHeaderStyle);
var tableContentStyle = new GC.Spread.Sheets.Style();
tableContentStyle.name = 'tableContent';
tableContentStyle.font = "normal normal 14.7px Calibri";
tableContentStyle.hAlign = 1;
spread.addNamedStyle(tableContentStyle);
var sourceStyle = new GC.Spread.Sheets.Style();
sourceStyle.name = 'source';
sourceStyle.hAlign = 0;
sourceStyle.backColor = "#fce8ce";
spread.addNamedStyle(sourceStyle);
var resultStyle = new GC.Spread.Sheets.Style();
resultStyle.name = 'result';
resultStyle.hAlign = 0;
resultStyle.backColor = "#e2efda";
spread.addNamedStyle(resultStyle);
}
function initSheet1(sheet) {
sheet.name('Use Case');
var table1Source = {
name: 'Quarterly Employee Commissions',
data: [
{ salesRap: 'Jim', quarter: 'Q1', revenue: 351 },
{ salesRap: 'Jim', quarter: 'Q2', revenue: 210 },
{ salesRap: 'Kevin', quarter: 'Q1', revenue: 687 },
{ salesRap: 'Sarah', quarter: 'Q1', revenue: 300 },
{ salesRap: 'Sarah', quarter: 'Q2', revenue: 809 },
{ salesRap: 'Kevin', quarter: 'Q2', revenue: 285 },
{ salesRap: 'Bob', quarter: 'Q1', revenue: 110 }
]
};
sheet.addSpan(1, 1, 1, 6);
sheet.setValue(1, 1, table1Source.name);
sheet.getCell(1, 1).hAlign(1).font("normal bold 15px Calibri");
sheet.setColumnWidth(1, 83);
sheet.setColumnWidth(2, 73);
sheet.setColumnWidth(3, 77);
sheet.setColumnWidth(4, 122);
sheet.setColumnWidth(5, 134);
sheet.setColumnWidth(6, 98);
var table1 = sheet.tables.add('Table1', 2, 1, 7, 6);
table1.style(GC.Spread.Sheets.Tables.TableThemes.medium2);
var table1Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "salesRap", "Sales Rap");
var table1Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "quarter", "Quarter");
var table1Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, "revenue", "Revenue");
var table1Column4 = new GC.Spread.Sheets.Tables.TableColumn(4, null, "Comm Category");
var table1Column5 = new GC.Spread.Sheets.Tables.TableColumn(5, null, "Comm Percentage", "0%");
var table1Column6 = new GC.Spread.Sheets.Tables.TableColumn(6, null, "Commission");
table1.autoGenerateColumns(false);
table1.bind([table1Column1, table1Column2, table1Column3, table1Column4, table1Column5, table1Column6], 'data', table1Source);
var table2Source = {
name: "Commissions Table",
data: [
{ category: 1, sales: 100, percentage: 0.05 },
{ category: 2, sales: 200, percentage: 0.1 },
{ category: 3, sales: 400, percentage: 0.15 },
{ category: 4, sales: 800, percentage: 0.20 }
]
};
sheet.addSpan(1, 8, 1, 3);
sheet.setValue(1, 8, table2Source.name);
sheet.getCell(1, 8).hAlign(1).font("normal bold 15px Calibri");
sheet.setColumnWidth(8, 88);
sheet.setColumnWidth(9, 57);
sheet.setColumnWidth(10, 91);
var table2 = sheet.tables.add('Table2', 2, 8, 4, 3);
table2.style(GC.Spread.Sheets.Tables.TableThemes.medium2);
var table2Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "category", "Category");
var table2Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "sales", "Sales");
var table2Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, "percentage", "Percentage", "0%");
table2.autoGenerateColumns(false);
table2.bind([table2Column1, table2Column2, table2Column3 ], 'data', table2Source);
table1.setColumnDataFormula(3, '=XMATCH([@Revenue],Table2[Sales],-1,1)');
table1.setColumnDataFormula(4, '=XLOOKUP([@[Comm Category]],Table2[Category],Table2[Percentage],0,0,1)');
table1.setColumnDataFormula(5, '=[@Revenue]*[@[Comm Percentage]]');
}
function initSheet2(sheet) {
sheet.name('basic exact match');
var intro = '#1 - basic exact match';
var formula = '=XMATCH(H5,B6:B10)';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
var data = [
["Movie","Year","Rank","Sales"],
["Fargo",1996,5,61],
["L.A. Confidential",1997,4,126],
["The Sixth Sense",1999,1,673],
["Toy Story",1995,2,362],
["Unforgiven",1992,3,159]
];
sheet.setArray(4, 1, data);
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j ++) {
var styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(4 + i, 1 + j, styleName);
}
}
sheet.setColumnWidth(1, 126);
sheet.setValue(4, 6, 'Movie');
sheet.setStyle(4, 6, 'source');
sheet.setValue(5, 6, 'Position');
sheet.setStyle(5, 6, 'result');
sheet.setValue(4, 7, 'Toy Story');
sheet.setFormula(5, 7, formula);
}
function initSheet3(sheet) {
sheet.name('basic approximate match');
var intro = '#2 - basic approximate match';
var formula = '=XMATCH(H5,E6:E10,1)';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
var data = [
["Movie","Year","Rank","Sales"],
["Fargo",1996,5,61],
["L.A. Confidential",1997,4,126],
["The Sixth Sense",1999,1,673],
["Toy Story",1995,2,362],
["Unforgiven",1992,3,159]
];
sheet.setArray(4, 1, data);
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j ++) {
var styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(4 + i, 1 + j, styleName);
}
}
sheet.setColumnWidth(1, 126);
sheet.setValue(4, 6, 'Sales');
sheet.setStyle(4, 6, 'source');
sheet.setValue(5, 6, 'Position');
sheet.setStyle(5, 6, 'result');
sheet.setValue(4, 7, 400);
sheet.setFormula(5, 7, formula);
}
function initSheet4(sheet) {
sheet.name('multiple values');
var intro = '#3 - multiple values';
var formula = '=XMATCH({5,4,1},D6:D10)';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
var data = [
["Movie","Year","Rank","Sales"],
["Fargo",1996,5,61],
["L.A. Confidential",1997,4,126],
["The Sixth Sense",1999,1,673],
["Toy Story",1995,2,362],
["Unforgiven",1992,3,159]
];
sheet.setArray(4, 1, data);
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j ++) {
var styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(4 + i, 1 + j, styleName);
}
}
sheet.setColumnWidth(1, 126);
sheet.setValue(4, 6, 'Rank');
sheet.setStyle(4, 6, 'source');
sheet.setValue(5, 6, 'Position');
sheet.setStyle(5, 6, 'result');
sheet.setValue(4, 7, '{5,4,1}');
sheet.setFormula(5, 7, formula);
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
</div>
</body>
</html>
input[type="text"] {
width: 200px;
margin-right: 20px;
}
label {
display: inline-block;
width: 110px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
label {
display: block;
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
code {
border: 1px solid #000;
}
