XMATCH | SpreadJS 15

XMATCH

In This Topic

XMATCH function searches for a specific item in an array or a range of cells and then returns the relative position of the item. In other words, this function can be used to find the position of an item in a list.

XMATCH function provides support for approximate matching, partial matching (using widcard characters like * , ? etc.) and exact matching.

Syntax

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Arguments

This function has the following arguments:

Argument	Description
lookup_value	Refers to the lookup value.
lookup_array	Refers to the lookup array or cell range that you want to search.
[match_mode]	[Optional] Specifies the type of the match as per the following values: 0 - Refers to exact match. If no matches are found, then #N/A error is returned. This is the default match mode. -1 - Refers to exact match. If no matches are found, then the next smaller item is returned. 1 - Refers to exact match. If no matches are found, then the next larger item is returned. 2 - Refers to a wildcard match where *, ? and ~ characters possess special meanings to indicate partial match.
[search_mode]	[Optional] Specifies the mode of the search as per the following values: 0 - Refers to the "search all" mode where all the matched values will be returned. [This mode is not available in Excel.] 1 - Refers to a search that starts at the first item. This is the default search mode. -1 - Refers to a reverse search that starts at the last item. 2 - Refers to a binary search that depends upon the lookup_array argument being sorted in the ascending order. -2 - Refers to a binary search that depends upon the lookup_array argument being sorted in the descending order.

Remarks

The XMATCH function can work with both vertical and horizontal arrays. This functions returns #N/A error if the lookup value is not found. XMATCH will return #VALUE! if using wildcard match mode and binary search mode at the same time.

Data Types

Accepts numeric data. Looks up values in a range or table. Returns an array with multiple items.

Examples

The following sample code show the usage of XMATCH function.

JavaScript	Copy Code
$(document).ready(function () { // Initializing Spread var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 }); // Enable dynamic array support spread.options.allowDynamicArray = true; // Get the activeSheet var sheet = spread.getSheet(0); // Set column width sheet.setColumnWidth(6, 160); // Create formula var formula_Exact = '=XMATCH(G4,A5:A9)'; var formula_Approx = '=XMATCH(G10,D5:D9,1)'; var formula_MultipleValues = '=XMATCH({5,4,1},C5:C9)'; // Set value sheet.setValue(0, 6, 'Basic exact match'); sheet.setValue(1, 6, formula_Exact); sheet.setValue(6, 6, 'Basic approximate match'); sheet.setValue(7, 6, formula_Approx); sheet.setValue(12, 6, 'MultipleValues'); sheet.setValue(13, 6, formula_MultipleValues); // Create data 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] ]; // Set data sheet.setArray(3, 0, data); // Set value sheet.setValue(3, 5, 'Movie'); sheet.setValue(4, 5, 'Position'); sheet.setValue(3, 6, 'Toy Story'); // Set formula sheet.setFormula(4, 6, formula_Exact); // Set value sheet.setValue(9, 5, 'Sales'); sheet.setValue(10, 5, 'Position'); sheet.setValue(9, 6, 400); // Set formula sheet.setFormula(10, 6, formula_Approx); // Set value sheet.setValue(15, 5, 'Rank'); sheet.setValue(16, 5, 'Position'); sheet.setValue(15, 6, '{5,4,1}'); // Set formula sheet.setFormula(16, 6, formula_MultipleValues); });

JavaScript

Copy Code

$(document).ready(function () {
    // Initializing Spread
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
    // Enable dynamic array support
    spread.options.allowDynamicArray = true;
    // Get the activeSheet
    var sheet = spread.getSheet(0);
    // Set column width
    sheet.setColumnWidth(6, 160);

    // Create formula
    var formula_Exact = '=XMATCH(G4,A5:A9)';
    var formula_Approx = '=XMATCH(G10,D5:D9,1)';
    var formula_MultipleValues = '=XMATCH({5,4,1},C5:C9)';

    // Set value
    sheet.setValue(0, 6, 'Basic exact match');
    sheet.setValue(1, 6, formula_Exact);
    sheet.setValue(6, 6, 'Basic approximate match');
    sheet.setValue(7, 6, formula_Approx);
    sheet.setValue(12, 6, 'MultipleValues');
    sheet.setValue(13, 6, formula_MultipleValues);
    // Create data
    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]
    ];
    // Set data
    sheet.setArray(3, 0, data);
    // Set value
    sheet.setValue(3, 5, 'Movie');
    sheet.setValue(4, 5, 'Position');
    sheet.setValue(3, 6, 'Toy Story');
    // Set formula
    sheet.setFormula(4, 6, formula_Exact);

    // Set value
    sheet.setValue(9, 5, 'Sales');
    sheet.setValue(10, 5, 'Position');
    sheet.setValue(9, 6, 400);
    // Set formula
    sheet.setFormula(10, 6, formula_Approx);

    // Set value
    sheet.setValue(15, 5, 'Rank');
    sheet.setValue(16, 5, 'Position');
    sheet.setValue(15, 6, '{5,4,1}');
    // Set formula
    sheet.setFormula(16, 6, formula_MultipleValues);

});

Syntax

Arguments

Remarks

Data Types

Examples

See Also