The INDEX function returns the value at the intersection of row number and column number. This function alone is not very effective, but when you combine it with the MATCH function, you can be the best alternative to the VLOOKUP function in previous versions of Office 2021, because in the 2021 version of Excel, a function named XLOOKUP replaced the VLOOKUP functions and the combination of the MATCH and INDEX functions. Stay with Seyed Ali Ebrahimi to learn the index function in Excel!
17 ways to increase internet speed
What is the Excel index function?
The Excel INDEX function retrieves a value or reference from a specific location within a range or array in your spreadsheet. It’s like pointing to a cell on a map with coordinates.
Here’s a basic breakdown:
- INDEX(array, row_num, [column_num])
- array: The range of cells containing the data you want to extract.
- row_num: The row number of the value you want (like Y coordinate on a map).
- [column_num] (optional): The column number of the value you want (like X coordinate on a map). If omitted, it defaults to the first column.
Example: If you have names in A1:A5 and ages in B1:B5, =INDEX(A1:A5,3,2) would return the name in cell A3 (3rd row), which corresponds to the age in cell B2 (2nd column because optional column_num is omitted).
The first argument (array) of the index function in Excel
The first argument (array) of the INDEX function in Excel is the foundation of the function. It specifies the range of cells or the array from which you want to extract a value. This range can be:
- A cell reference: A single cell containing a value (technically a 1×1 array).
- A range of cells: A rectangular area of cells containing multiple values.
- An array constant: A set of values directly typed within curly braces { } (rarely used).
Here’s why the array argument is crucial:
- It defines the pool of data the INDEX function searches through.
- The row_num and optional column_num arguments (second and third arguments) use this array as a reference to pinpoint the specific value you want to extract.
Examples of valid array arguments:
- A1:B5 (range of cells)
- “apple” (single cell reference containing text)
- {10, 20, 30} (array constant)
Remember, for the INDEX function to work effectively, the array argument must contain the data you’re looking for.
The second argument (row_num) of the index function in Excel
The second argument (row_num) of the INDEX function in Excel acts like a vertical coordinate, pinpointing the desired row within the array (data range) you specified in the first argument. Here’s a closer look at its role:
Function of row_num:
- It tells the INDEX function which row to extract a value from within the provided array.
- It must be a positive integer (1, 2, 3, etc.) corresponding to the actual row number in your data range.
Using row_num effectively:
- Together with the optional third argument (column_num), it pinpoints the exact cell location within the array.
- If the row_num is greater than the number of rows in the array, the function will return an error (#REF!).
Examples:
- =INDEX(A1:B5, 2) – Extracts the value from the second row (row 2) of the range A1:B5.
- =INDEX({“apple”, “banana”, “cherry”}, 1) – Extracts “apple” from the first row (row 1) of the array {“apple”, “banana”, “cherry”}.
Additional Notes:
- You can use a cell reference containing a row number instead of a literal number for row_num. This allows for dynamic referencing based on other calculations in your spreadsheet.
- An important concept to remember is that unlike some other spreadsheet programs, Excel starts row numbering from 1, not 0.
By effectively using the row_num argument, you can efficiently target specific rows within your data range and extract the desired values using the INDEX function.
The third argument (column_num) of the index function in Excel
The third argument (column_num) of the INDEX function in Excel acts like a horizontal coordinate, working alongside the row_num (second argument) to pinpoint the exact cell location within the array (data range) you specified in the first argument. Here’s a breakdown of its functionality:
What column_num does:
- It’s an optional argument that specifies the column number from which you want to extract a value.
- It works in conjunction with row_num to create a precise cell reference within the array.
- If omitted, the INDEX function defaults to extracting the value from the first column (column A) of the array.
Using column_num effectively:
- When combined with row_num, it allows you to target any specific cell within the data range.
- If the column_num is greater than the number of columns in the array, the function will return an error (#REF!).
Examples:
- =INDEX(A1:B5, 2, 1) – Extracts the value from cell A2 (second row, first column) of the range A1:B5.
- =INDEX({“apple”, “banana”, “cherry”}, 3, 2) – Extracts “cherry” from cell B3 (third row, second column) of the array {“apple”, “banana”, “cherry”}.
Additional Notes:
- Similar to row_num, you can use a cell reference containing a column number instead of a literal number for column_num. This enables dynamic referencing based on other calculations in your spreadsheet.
- Excel follows a zero-based column indexing system. This means the first column is column 0, the second column is column 1, and so on. Be mindful of this when using cell references for column_num.
The fourth argument (area_num) of the index function in Excel
The INDEX function in Excel actually only has three arguments: array, row_num, and column_num. There’s no built-in fourth argument called “area_num”.
There might be some confusion arising from two possible scenarios:
Reference Form of INDEX: Excel offers a reference form of the INDEX function that can be used in certain situations. This form allows you to reference multiple ranges (areas) within a single formula. However, it doesn’t involve a separate fourth argument.
Other Spreadsheet Software: Some other spreadsheet programs might have a function similar to INDEX with an “area_num” argument. It’s possible you encountered this concept in a different software and mistakenly applied it to Excel.
Here’s a clarification of the two scenarios:
Reference Form (Excel Specific): This form involves using multiple ranges within the first argument (array) of the INDEX function. Let’s say you have data in A1:B5 and C1:D5. You can use
=INDEX((A1:B5,C1:D5),2,2)to extract the value from the second row (row 2) and second column (column B) of the first area (A1:B5). In this case, you’re essentially combining two ranges into a single “array” for the INDEX function to work with.Other Software: Some spreadsheet programs might have a separate function where the fourth argument specifies which area (range) within a larger structure to consider. This functionality might not be directly equivalent to Excel’s INDEX function.
If you can provide more context about where you encountered the “area_num” argument, I might be able to offer a more specific explanation.
The structure of the INDEX function in Excel
The INDEX function in Excel has a well-defined structure with three core arguments:
1. array (Required): This is the foundation of the function. It specifies the range of cells or the array from which you want to extract a value. This can be:
- A single cell reference (technically a 1×1 array).
- A range of cells (rectangular area containing multiple values).
- An array constant (a set of values directly typed within curly braces { } – rarely used).
2. row_num (Required): This argument acts like a vertical coordinate, pinpointing the desired row within the array you specified in the first argument. It must be a positive integer (1, 2, 3, etc.) corresponding to the actual row number in your data range.
3. column_num (Optional): This argument acts like a horizontal coordinate, working in conjunction with row_num to pinpoint the exact cell location. It specifies the column number from which you want to extract a value.
- If omitted, the function defaults to extracting the value from the first column (column A) of the array.
- It must also be a positive integer corresponding to the actual column number in your data range.
Here’s the basic formula structure:
=INDEX(array, row_num, [column_num])
Important points to remember:
- All three arguments are enclosed in parentheses.
- row_num and column_num (if used) must be separated by commas.
- If either row_num or column_num is greater than the dimensions of the array, the function will return an error (#REF!).
Additional Notes:
- You can use cell references containing row or column numbers instead of literal integers for row_num and column_num. This allows for dynamic referencing based on other calculations in your spreadsheet.
- Excel uses a zero-based column indexing system, meaning the first column is column 0, the second is column 1, and so on. Be mindful of this when using cell references for column_num.
INDEX function with more than one condition
The INDEX function itself in Excel can’t handle multiple conditions directly within its formula. However, there are a couple of common workarounds to achieve what you want:
1. MATCH Function with INDEX:
This approach combines INDEX with the MATCH function to locate the desired value based on multiple criteria. Here’s how it works:
- Use the MATCH function to find the row number that meets your combined criteria within a specific lookup array.
- Use that row number along with the INDEX function to extract the corresponding value from a separate result array.
Example:
Imagine you have data on product ID (A1:A10), color (B1:B10), and price (C1:C10). You want to find the price of a specific product (ID = 123) that’s also red (color).
Here’s the formula breakdown:
- Find the row number using MATCH:
=MATCH(123, A1:A10, 0)(exact match for product ID 123 in A1:A10) - Use the row number with INDEX to get the price:
=INDEX(C1:C10, MATCH(123, A1:A10, 0))
2. Array Formulas with Helper Columns:
This method involves creating helper columns with formulas that evaluate your multiple conditions. Then, you can use INDEX based on the outcome of these helper columns.
Example:
Following the previous scenario, you can create a helper column (D1:D10) with a formula like: =AND(A1=123, B1="Red"). This checks for both product ID and color.
Then, use INDEX to extract the price based on the TRUE/FALSE values in the helper column: =INDEX(C1:C10, ROW(D1:D10)*D1:D10). Here, ROW(D1:D10) creates an array of row numbers, and D1:D10 checks the TRUE/FALSE values to return the corresponding price.
Important Note:
- Array formulas require pressing Ctrl+Shift+Enter instead of just Enter to enter them correctly. They are enclosed in curly braces { } automatically.
These are two common ways to use the INDEX function along with other functions or helper columns to achieve results based on multiple criteria in Excel.
Is it always possible to use INDEX instead of VLOOKUP?
No, it’s not always possible to directly substitute VLOOKUP with INDEX in Excel. While INDEX offers more flexibility, there are some situations where VLOOKUP might be a better fit:
Scenarios Favoring VLOOKUP:
Simple Lookups: For basic lookups where the data you’re referencing is in the first column of the table, VLOOKUP can be simpler to use. It requires only the lookup value, table array, and column index (the position of the return value within the table).
Limited Functionality Needed: If you only need to perform a straightforward lookup without complex criteria or calculations, VLOOKUP might be sufficient.
Limitations of INDEX for Direct Replacement:
Lookup Value Not in First Column: INDEX requires you to specify the row number for retrieval. If your lookup value isn’t in the first column of the table, you’ll need to use the MATCH function or other methods to find the row number dynamically, making the formula more complex.
Extracting Data from Different Columns: VLOOKUP allows you to specify the column index within the table to retrieve the desired data. INDEX, by default, retrieves from the first column. To get data from other columns, you’ll need the row number and an additional column number argument.