XLOOKUP function

PRO

The XLOOKUP function in Jspreadsheet Formulas Pro is a tool that helps you find specific values within a set range or an array. You provide it with a value to search for, and it will scan the specified range or array for that value. Then, XLOOKUP gives you the corresponding value from a different range or array that occupies the same position as the located value. This function is useful when you want to correlate data between different lists or tables.

Documentation

Searches a range or an array for a specified value and returns a corresponding value in the same position from another range or array.

Syntax

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])

Parameter	Description
`lookup_value`	The value to search for.
`lookup_array`	The range or array to search.
`return_array`	The range or array to return a value from.
`match_mode`	Optional. Specifies how the function matches lookup_value with values in lookup_array. 0 → Exact match (default), -1 → Exact match or next smaller item, 1 → Exact match or next larger item, 2 → Wildcard match (using * or ?)
`search_mode`	Optional. Specifies whether the function searches for an exact or approximate match. 1 → Search from first to last (default), -1 → Search from last to first

Behavior

The XLOOKUP function is designed to return a value in a list or array, based on a matched value in a different list or array. Here are some of its behaviors:

XLOOKUP can perform a lookup to the left or the right. This means it does not entirely depend on looking up data on the right of the lookup value.
If the lookup_value is smaller than the smallest value in the lookup_array, XLOOKUP will return the #N/A error.
If the lookup_value is larger than the largest value in the lookup_array and match_mode is set to 1, XLOOKUP will return the #N/A error.
XLOOKUP is case-insensitive. It does not distinguish between lowercase and uppercase text.
If an array is empty, XLOOKUP will return #VALUE! error.
It treats both text and Boolean values as legitimate lookup values.
XLOOKUP can return a single value or an array of values (horizontal or vertical).

Common Errors

Error	Description
`#N/A`	This error occurs when `XLOOKUP` cannot find the `lookup_value`.
`#VALUE!`	This error occurs if `lookup_array` and `return_array` have mismatched sizes or if the input types are invalid.
`#REF!`	This error is returned when the given cell reference is not valid.

Best practices

Always ensure that your lookup_array and return_array are of the same length to avoid #VALUE! errors.

Use absolute cell references when copying and pasting your XLOOKUP formula to other cells to keep the array references constant.

Specify the if_not_found parameter to handle instances where the lookup_value is not found in the lookup_array.

Use the match_mode argument to define how XLOOKUP matches the lookup_value with values in lookup_array. It determines whether an exact match is needed or a closest match is acceptable.

Usage

A few examples using the XLOOKUP function.

XLOOKUP("apple", ["apple","banana","cherry"], [5, 10, 15]) returns 5  
XLOOKUP(2, [1,2,3], ["one","two","three"], -1) returns "two"  
XLOOKUP("green", ["red","yellow","green","blue"], ["roses","sunflowers","grass","ocean"], ,1) returns "grass"

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Product ID",
        "Product Name",
        "Price",
        "Lookup ID",
        "Found Product"
    ],
    [
        "P001",
        "Laptop",
        999,
        "P003",
        "=XLOOKUP(D2,A2:A4,B2:B4)"
    ],
    [
        "P002",
        "Mouse",
        25,
        "P001",
        "=XLOOKUP(D3,A2:A4,B2:B4)"
    ],
    [
        "P003",
        "Keyboard",
        75,
        "P002",
        "=XLOOKUP(D4,A2:A4,B2:B4)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Product ID",
        "Product Name",
        "Price",
        "Lookup ID",
        "Found Product"
    ],
    [
        "P001",
        "Laptop",
        999,
        "P003",
        "=XLOOKUP(D2,A2:A4,B2:B4)"
    ],
    [
        "P002",
        "Mouse",
        25,
        "P001",
        "=XLOOKUP(D3,A2:A4,B2:B4)"
    ],
    [
        "P003",
        "Keyboard",
        75,
        "P002",
        "=XLOOKUP(D4,A2:A4,B2:B4)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Product ID",
        "Product Name",
        "Price",
        "Lookup ID",
        "Found Product"
    ],
    [
        "P001",
        "Laptop",
        999,
        "P003",
        "=XLOOKUP(D2,A2:A4,B2:B4)"
    ],
    [
        "P002",
        "Mouse",
        25,
        "P001",
        "=XLOOKUP(D3,A2:A4,B2:B4)"
    ],
    [
        "P003",
        "Keyboard",
        75,
        "P002",
        "=XLOOKUP(D4,A2:A4,B2:B4)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Product ID",
        "Product Name",
        "Price",
        "Lookup ID",
        "Found Product"
    ],
    [
        "P001",
        "Laptop",
        999,
        "P003",
        "=XLOOKUP(D2,A2:A4,B2:B4)"
    ],
    [
        "P002",
        "Mouse",
        25,
        "P001",
        "=XLOOKUP(D3,A2:A4,B2:B4)"
    ],
    [
        "P003",
        "Keyboard",
        75,
        "P002",
        "=XLOOKUP(D4,A2:A4,B2:B4)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub