Table of Contents
The INDEX function in Google Sheets allows users to retrieve data from a specific cell or range within a spreadsheet. It requires two main inputs: the range of cells to be searched and the row and column numbers of the desired cell. This function is useful for organizing and analyzing large sets of data, as it simplifies the process of locating and retrieving specific information. To create an INDEX function in Google Sheets, users can follow the simple steps outlined in the program’s formula bar. By utilizing this function, users can efficiently manage and manipulate their data to make informed decisions and streamline their workflow.
INDEX
Returns the content of a cell, specified by row and column offset.
Examples
Make a copy
| Guest Name | Dietary Restriction | Sent Invitation | Table Number |
|---|---|---|---|
| David | Vegetarian | No | 3 |
| Bob | None | No | 5 |
| david | None | Yes | 1 |
| Nancy | None | No | 4 |
| Mary | Vegetarian | Yes | 2 |
| Formula | Formula Output |
|---|---|
| =INDEX(A2:D6, 2, 1) | Bob |
| =INDEX(A2:D6, 4, 4) | 4 |
| =INDEX(A2:D6,1,1) | David |
| =INDEX(A1:D6, 6, 2) | Vegetarian |
| Formula | Formula Output | |||
|---|---|---|---|---|
| =INDEX(A2:D6, 2, 0) | Bob | None | No | 5 |
| Formula | Formula Output |
|---|---|
| =INDEX(A2:D6, 0, 4) | 3 |
| 5 | |
| 1 | |
| 4 | |
| 2 |
Returns the cell(s) found by index value into the referenced range.
Sample Usage
INDEX(A1:C20, 5, 1)
Syntax
INDEX(reference, [row], [column])
reference– The range of cells from which the values are returned.row– [OPTIONAL – 0 by default] – The index of the row to be returned from within the reference range of cells.column– [OPTIONAL –0by default] – The index of the column to be returned from within the reference range of cells.
Using INDEX and Match
INDEX and MATCH can be used together to perform more advanced and dynamic lookups.
- Tip: VLOOKUP can be used when the lookup value is to the left of the desired attribute to return. INDEX and MATCH can be used regardless of where the lookup value is located in the dataset.
| Guest Name | Dietary Restriction | Sent Invitation | Table Number |
|---|---|---|---|
| David | Vegetarian | No | 3 |
| Bob | None | No | 5 |
| david | None | Yes | 1 |
| Nancy | None | No | 4 |
| Mary | Vegetarian | Yes | 2 |
| Goal | Formula | Formula Output | Comment |
|---|---|---|---|
| Find Mary’s Dietary Restriction | =VLOOKUP(“Mary”, A1:D6, 2, false) | Vegetarian | Both search key and index are hardcoded |
| Find Mary’s Dietary Restriction | =INDEX(A1:D6, MATCH(“Mary”, A1:A6, 0), 2) | Vegetarian | Row number is dynamic & column number is hardcoded |
| Find Mary’s Dietary Restriction | =INDEX(A1:D6, MATCH(“Mary”, A1:A6, 0), MATCH(“Dietary Restriction”, A1:D1, 0)) | Vegetarian | Both row number & column number are dynamic |
| Find Who is at Table Number 2 | Using VLOOKUP, this would not be possible | N/A | VLOOKUP can only be used when the lookup value is to the left of the desired attribute to return |
| Find Who is at Table Number 2 | =INDEX(A1:D6, MATCH(2, D1:D6, 0), MATCH(“Guest Name”, A1:D1, 0)) | Mary | INDEX and MATCH can be used regardless of where the lookup value is located relative to the desired attribute to return |
See Also
MATCH: Returns the relative position of an item in a range that matches a specified value.
OFFSET: Returns a range reference shifted a specified number of rows and columns from a starting cell reference.
Notes
- If you set row or column to 0,
INDEXreturns the array of values for the entire column or row, respectively.
Cite this article
stats writer (2024). How do I create an INDEX function in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-create-an-index-function-in-google-sheets/
stats writer. "How do I create an INDEX function in Google Sheets?." PSYCHOLOGICAL SCALES, 29 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-create-an-index-function-in-google-sheets/.
stats writer. "How do I create an INDEX function in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-create-an-index-function-in-google-sheets/.
stats writer (2024) 'How do I create an INDEX function in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-create-an-index-function-in-google-sheets/.
[1] stats writer, "How do I create an INDEX function in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I create an INDEX function in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.