CHOOSECOLS function

PRO

The CHOOSECOLS function in Jspreadsheet Formulas Pro is a handy tool that allows you to select specific columns from a table based on their position. For instance, if you have a table with multiple columns and you only want to work with the 1st, 3rd, and 5th columns, you can use CHOOSECOLS to easily select them. This function is particularly useful when dealing with large data sets, as it simplifies the process of selecting and working with specific columns.

Documentation

Returns a range of columns selected from a table based on their position in the table.

Syntax

CHOOSECOLS(table, column_index1, [column_index2], ...)

Parameter	Description
`table`	A range of cells that represents the table.
`column_index1`	The index number of the first column you want to select. Must be a positive integer.
`[column_indexN]`	Optional. Additional index numbers of columns you want to select.

Behavior

The CHOOSECOLS function is used to select specific columns from a range or a table of data. Here's how it generally handles different data types:

Empty Cells: CHOOSECOLS returns all values from the selected columns, including empty cells. Empty columns do not cause an error unless the range is invalid.
Text: This function works well with text values and returns them as they are in the selected columns.
Booleans: Boolean values (TRUE/FALSE) are treated as regular values and returned as they are.
Errors: If there is an error in a cell within the chosen columns, the function will return that error.

Common Errors

Error	Description
#VALUE!	This error occurs when the columns selected do not exist in the specified range or when the function cannot find the specified columns.
#REF!	This error is returned when the columns selected are outside the bounds of the current spreadsheet, or if the chosen columns have been deleted.
#NUM!	This error happens when the column index given is non-numeric.

Best practices

Always ensure the columns you want to select with CHOOSECOLS exist within the specified range to avoid the #VALUE! error.

Use the CHOOSECOLS function with other functions to manipulate or analyze data from the selected columns for better efficiency.

Be cautious when deleting columns from your spreadsheet if those columns are referenced in a CHOOSECOLS function, as this can result in a #REF! error.

Make sure your spreadsheet software supports the CHOOSECOLS function to avoid the #NAME? error.

Usage

A few examples using the CHOOSECOLS function.

CHOOSECOLS(A1:D5, 1, 3)  
// Returns columns A and C from the range A1:D5

CHOOSECOLS(A1:D5, 2, 4)  
// Returns columns B and D from the range

CHOOSECOLS(A1:E10, 1, 3, 5)  
// Returns columns A, C, and E from the full range

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Product",
        "Price",
        "Stock",
        "Category",
        "Supplier"
    ],
    [
        "Laptop",
        899,
        25,
        "Electronics",
        "TechCorp"
    ],
    [
        "Mouse",
        29,
        150,
        "Electronics",
        "AccessCo"
    ],
    [
        "Desk",
        249,
        8,
        "Furniture",
        "OfficePlus"
    ],
    [
        "Chair",
        179,
        12,
        "Furniture",
        "OfficePlus"
    ],
    [],
    [
        "=CHOOSECOLS(A1:E5,1,3,4)"
    ]
]
  }]
});
</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('ZTgyOTc4YjMxODI0MTg4MDM1NTcwNDdlNzFlMDkwNzhlMzIwMGRjMzA3NzdkNWYzZTVkYWQwODNkZDhiODA5Mzc3ZGNmMjU3MTNkMjUzNWUyOWQ2NDEzY2E0ZmIxMTRlNDQxODcyZDU3ZGU3ZTVmZWIxM2ZmNTZjNWFiNzdiNzAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56ZzJOVEl3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Product",
        "Price",
        "Stock",
        "Category",
        "Supplier"
    ],
    [
        "Laptop",
        899,
        25,
        "Electronics",
        "TechCorp"
    ],
    [
        "Mouse",
        29,
        150,
        "Electronics",
        "AccessCo"
    ],
    [
        "Desk",
        249,
        8,
        "Furniture",
        "OfficePlus"
    ],
    [
        "Chair",
        179,
        12,
        "Furniture",
        "OfficePlus"
    ],
    [],
    [
        "=CHOOSECOLS(A1:E5,1,3,4)"
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Product",
        "Price",
        "Stock",
        "Category",
        "Supplier"
    ],
    [
        "Laptop",
        899,
        25,
        "Electronics",
        "TechCorp"
    ],
    [
        "Mouse",
        29,
        150,
        "Electronics",
        "AccessCo"
    ],
    [
        "Desk",
        249,
        8,
        "Furniture",
        "OfficePlus"
    ],
    [
        "Chair",
        179,
        12,
        "Furniture",
        "OfficePlus"
    ],
    [],
    [
        "=CHOOSECOLS(A1:E5,1,3,4)"
    ]
]

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

// 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",
        "Stock",
        "Category",
        "Supplier"
    ],
    [
        "Laptop",
        899,
        25,
        "Electronics",
        "TechCorp"
    ],
    [
        "Mouse",
        29,
        150,
        "Electronics",
        "AccessCo"
    ],
    [
        "Desk",
        249,
        8,
        "Furniture",
        "OfficePlus"
    ],
    [
        "Chair",
        179,
        12,
        "Furniture",
        "OfficePlus"
    ],
    [],
    [
        "=CHOOSECOLS(A1:E5,1,3,4)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub