LOOKUP function

PRO

The LOOKUP function in Jspreadsheet Formulas Pro is a useful tool for finding a specific value within a certain range or array. This function operates by identifying a value you specify within a given set, then provides the corresponding value from a different set that is positioned in the same way. Essentially, it's like having a digital assistant that can find the 'match' of a piece of data from one list in another list. This feature can save you a significant amount of time when working with large or complex data sets.

Documentation

Searches for a value in a range or array and returns a value in the same position from a second range or array.

Syntax

LOOKUP(lookup_value, lookup_array, [result_array])

Parameter	Description
`lookup_value`	The value to search for in the lookup_array. The lookup_value can be a number, text, logical value, or a reference to a cell containing any of these.
`lookup_array`	The range of cells or an array that contains the values to be searched. The lookup_array must be one row or one column.
`result_array`	Optional. The range of cells or an array that contains the values to be returned. If this argument is omitted, the function returns the corresponding value from the lookup_array.

Behavior

The LOOKUP function in spreadsheets is used to search and retrieve data from a specific column or row in a range. It is commonly used when you need to look up and match data in different sheets or tables.

Here's how LOOKUP handles different types of data:

Empty Cells: LOOKUP ignores empty cells when searching. If no match is found, it returns a #N/A error.
Text: LOOKUP is case-insensitive. If the search key is a text string, it will return the first matching value regardless of case.
Booleans: LOOKUP can handle boolean values (TRUE or FALSE). If the lookup value is boolean, it matches the boolean value in lookup vector.
Errors: If the LOOKUP function encounters errors in the lookup vector or result vector, it will return an error.

Common Errors

Error	Description
#N/A	This error occurs when `LOOKUP` cannot find the lookup value in the lookup range.
#REF!	This error is returned when the formula contains an invalid cell reference.
#VALUE!	This error is returned if the wrong type of argument or operand is used.

Best practices

Always ensure that the data in the lookup column is sorted in ascending order. If the data is not sorted, LOOKUP may return incorrect results.

Be careful with text and numeric values. If your lookup value is numeric and the lookup vector contains text, LOOKUP will return #N/A.

Ensure that the lookup_array is a single row or a single column. If you need two-dimensional lookups, consider using INDEX/MATCH or other lookup functions.

Handle errors properly by using functions like IFERROR to ensure your spreadsheet continues to work smoothly even when errors occur.

Usage

A few examples using the LOOKUP function.

LOOKUP(2, A1:A10, B1:B10)  
// Searches for the value 2 in A1:A10 and returns the corresponding value from B1:B10.

LOOKUP("apples", A1:A10, B1:B10)  
// Searches for "apples" in A1:A10 and returns the corresponding value from B1:B10.

LOOKUP(0.5, {0,0.25,0.5,0.75,1}, {"F","D","C","B","A"})  
// Searches for 0.5 in the first array and returns "C" from the second array.

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('ZjNmYTJhYzAzNzA3YjQ1YzY0OWYwN2MwY2I3MmU5MWYyNDJmM2FjZWEwYzBlMTFlMjlmZmJiZWVhMDUxZTc1MDJmNDc5MjZkNDE0NTNmMTE2NjU2MjU3NjEyNDA1M2I3MDYyMWM2Nzg2YjZhOWY2ZjkyMWI0YzEwMTJjMjMzYmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNek00TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Product",
        "Price",
        "Lookup Result"
    ],
    [
        "Apples",
        2.5
    ],
    [
        "Bananas",
        1.75
    ],
    [
        "Oranges",
        3.0
    ],
    [
        "Bananas",
        "=LOOKUP(A5,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('ZjNmYTJhYzAzNzA3YjQ1YzY0OWYwN2MwY2I3MmU5MWYyNDJmM2FjZWEwYzBlMTFlMjlmZmJiZWVhMDUxZTc1MDJmNDc5MjZkNDE0NTNmMTE2NjU2MjU3NjEyNDA1M2I3MDYyMWM2Nzg2YjZhOWY2ZjkyMWI0YzEwMTJjMjMzYmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNek00TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Product",
        "Price",
        "Lookup Result"
    ],
    [
        "Apples",
        2.5
    ],
    [
        "Bananas",
        1.75
    ],
    [
        "Oranges",
        3.0
    ],
    [
        "Bananas",
        "=LOOKUP(A5,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('ZjNmYTJhYzAzNzA3YjQ1YzY0OWYwN2MwY2I3MmU5MWYyNDJmM2FjZWEwYzBlMTFlMjlmZmJiZWVhMDUxZTc1MDJmNDc5MjZkNDE0NTNmMTE2NjU2MjU3NjEyNDA1M2I3MDYyMWM2Nzg2YjZhOWY2ZjkyMWI0YzEwMTJjMjMzYmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNek00TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Product",
        "Price",
        "Lookup Result"
    ],
    [
        "Apples",
        2.5
    ],
    [
        "Bananas",
        1.75
    ],
    [
        "Oranges",
        3.0
    ],
    [
        "Bananas",
        "=LOOKUP(A5,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('ZjNmYTJhYzAzNzA3YjQ1YzY0OWYwN2MwY2I3MmU5MWYyNDJmM2FjZWEwYzBlMTFlMjlmZmJiZWVhMDUxZTc1MDJmNDc5MjZkNDE0NTNmMTE2NjU2MjU3NjEyNDA1M2I3MDYyMWM2Nzg2YjZhOWY2ZjkyMWI0YzEwMTJjMjMzYmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNek00TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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",
        "Price",
        "Lookup Result"
    ],
    [
        "Apples",
        2.5
    ],
    [
        "Bananas",
        1.75
    ],
    [
        "Oranges",
        3.0
    ],
    [
        "Bananas",
        "=LOOKUP(A5,A2:A4,B2:B4)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub