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.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
</div>
</template>
<script setup>
import '@mescius/spread-sheets-vue';
import { ref } from "vue";
import GC from "@mescius/spread-sheets";
const spreadRef = ref(null);
let initSpread = function (spread) {
spreadRef.value = spread;
spread.options.allowDynamicArray = true;
initStyles(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();
}
let initStyles = function (spread) {
let introStyle = new GC.Spread.Sheets.Style();
introStyle.name = 'intro';
introStyle.font = 'normal bold 16px Segoe UI';
introStyle.foreColor = "#172b4d";
spread.addNamedStyle(introStyle);
let 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);
let formulaStyle = new GC.Spread.Sheets.Style();
formulaStyle.name = 'formula';
formulaStyle.font = 'normal bold 12px Consolas';
formulaStyle.foreColor = "#c00000";
introStyle1.vAlign = 1;
spread.addNamedStyle(formulaStyle);
let 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);
let tableContentStyle = new GC.Spread.Sheets.Style();
tableContentStyle.name = 'tableContent';
tableContentStyle.font = "normal normal 14.7px Calibri";
tableContentStyle.hAlign = 1;
spread.addNamedStyle(tableContentStyle);
let sourceStyle = new GC.Spread.Sheets.Style();
sourceStyle.name = 'source';
sourceStyle.hAlign = 0;
sourceStyle.backColor = "#fce8ce";
spread.addNamedStyle(sourceStyle);
let resultStyle = new GC.Spread.Sheets.Style();
resultStyle.name = 'result';
resultStyle.hAlign = 0;
resultStyle.backColor = "#e2efda";
spread.addNamedStyle(resultStyle);
}
let initSheet1 = function (sheet) {
sheet.name('Use Case');
let 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);
let table1 = sheet.tables.add('Table1', 2, 1, 7, 6);
table1.style(GC.Spread.Sheets.Tables.TableThemes.medium2);
let table1Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "salesRap", "Sales Rap");
let table1Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "quarter", "Quarter");
let table1Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, "revenue", "Revenue");
let table1Column4 = new GC.Spread.Sheets.Tables.TableColumn(4, null, "Comm Category");
let table1Column5 = new GC.Spread.Sheets.Tables.TableColumn(5, null, "Comm Percentage", "0%");
let table1Column6 = new GC.Spread.Sheets.Tables.TableColumn(6, null, "Commission");
table1.autoGenerateColumns(false);
table1.bind([table1Column1, table1Column2, table1Column3, table1Column4, table1Column5, table1Column6], 'data', table1Source);
let 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);
let table2 = sheet.tables.add('Table2', 2, 8, 4, 3);
table2.style(GC.Spread.Sheets.Tables.TableThemes.medium2);
let table2Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "category", "Category");
let table2Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "sales", "Sales");
let 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]]');
}
let initSheet2 = function (sheet) {
sheet.name('basic exact match');
let intro = '#1 - basic exact match';
let 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');
let 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 (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j ++) {
let 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);
}
let initSheet3 = function (sheet) {
sheet.name('basic approximate match');
let intro = '#2 - basic approximate match';
let 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');
let 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 (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j ++) {
let 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);
}
let initSheet4 = function (sheet) {
sheet.name('multiple values');
let intro = '#3 - multiple values';
let 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');
let 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 (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j ++) {
let 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);
}
</script>
<style scoped>
#app {
height: 100%;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
#switchAutoMergeMode {
margin: 10px 0px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-en': 'npm:@mescius/spread-sheets-resources-en/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);