Home › Functions › Lookup & Reference › =INDEX()
Lookup & Reference All versions ◉ 40 views

=INDEX()

Returns the value of an element in a table or an array, selected by the row and column number indexes. Use the array form if the first argument to INDEX is an array constant. Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of non-adjacent selections, you can pick the selection to look in.

Applies To: Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016
=INDEX(array, row_num, [column_num])
Hover argument names in the table for details.
Output typeMixed
Popularity95 / 100
VolatileNo
Dynamic ArrayNo
📥 Download sample .xlsx ▦ Try in Playground Dedicated file for this function — safe & lightweight · sample data + live formulas
Share:

⬇ Download Sample Workbook

A dedicated .xlsx for =INDEX() with Persian sample data & live formulas — open in Excel and practice.

  • Sheet “Sample” — data table + formula in place
  • Sheet “Guide” — syntax, arguments & tips from this page
  • Instant & secure (token + rate limit)
📥 Download INDEX.xlsx ~ 8–12 KB · No macros

⇪ Share

Send this function to friends

🧠 🧠 Quiz this function

%d questions — syntax, example & errors — test yourself!

◧ Arguments

ArgumentTypeRequiredDescription
array Range Required A range of cells or an array constant. If array contains only one row or column, the corresponding row_num or column_num argument is optional. If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
row_num Number Required , unless column_num is present. Selects the row in array from which to return a value. If row_num is omitted, column_num is
column_num Number Required Selects the column in array from which to return a value. If column_num is omitted, row_num is
reference Range Required A reference to one or more cell ranges. If you are entering a non-adjacent range for the reference, enclose reference in parentheses. If each area in reference contains only one row or column, the row_num or column_num argument, respectively, is optional. For example, for a single row reference, use INDEX(reference,,column_num).
area_num Range Optional Selects a range in reference from which to return the intersection of row_num and column_num. The first area selected or entered is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX uses area 1. The areas listed here must all be located on one sheet. If you specify areas that are not on the same sheet as each other, it will cause a #VALUE! error. If you need to use ranges that are located on different sheets from each other, it is recommended that you use the array form of the INDEX function, and use another function to calculate the range that makes up the array. For example, you could use the CHOOSE function to calculate which range will be used.

▣ Examples

▦ Data
CellValue
A2 Apples
B2 Lemons
A3 Bananas
B3 Pears
A4 Fruit
B4 Price
C4 Count
A5 Apples
B5 $0.69
C5 40
A6 Bananas
B6 $0.34
C6 38
A7 Lemons
B7 $0.55
C7 15
A8 Oranges
B8 $0.25
C8 25
A9 Pears
B9 $0.59
C9 40
A10 Almonds
B10 $2.80
C10 10
A11 Cashews
B11 $3.55
C11 16
A12 Peanuts
B12 $1.25
C12 20
A13 Walnuts
B13 $1.75
C13 12
A14 Apples
B14 Lemons
A15 Bananas
B15 Pears
A16 Fruit
B16 Price
C16 Count
A17 Apples
B17 $0.69
C17 40
A18 Bananas
B18 $0.34
C18 38
A19 Lemons
B19 $0.55
C19 15
A20 Oranges
B20 $0.25
C20 25
A21 Pears
B21 $0.59
C21 40
A22 Almonds
B22 $2.80
C22 10
A23 Cashews
B23 $3.55
C23 16
A24 Peanuts
B24 $1.25
C24 20
A25 Walnuts
B25 $1.75
C25 12
Value at the intersection of the second row and second column in the range A2:B3. =INDEX(A2:B3,2,2)
Result: Pears
Value at the intersection of the second row and first column in the range A2:B3. =INDEX(A2:B3,2,1)
Result: Bananas
Value found in the first row, second column in the array. The array contains 1 and 2 in the first row and 3 and 4 in the second row. =INDEX({1,2⁠;⁠3,4},0,2)
Result: 2
The intersection of the second row and third column in the range A2:C6, which is the contents of cell C3. =INDEX(A2:C6, 2, 3)
Result: 38
The intersection of the second row and second column in the second area of A8:C11, which is the contents of cell B9. =INDEX((A1:C6, A8:C11), 2, 2, 2)
Result: 1.25
The sum of the third column in the first area of the range A1:C11, which is the sum of C1:C11. =SUM(INDEX(A1:C11, 0, 3, 1))
Result: 216
The sum of the range starting at B2, and ending at the intersection of the fifth row and the second column of the range A2:A6, which is the sum of B2:B6. =SUM(B2:INDEX(A2:C6, 5, 2))
Result: 2.42

§ Remarks

  • If both the row_num and column_num arguments are used, INDEX returns the value in the cell at the intersection of row_num and column_num.
  • row_num and column_num must point to a cell within array; otherwise, INDEX returns a #REF! error.
  • If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula. Note If you have a current version of Microsoft 365, then you can input the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, input the formula in the top-left-cell of the output range, then press CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.
  • After reference and area_num have selected a particular range, row_num and column_num select a particular cell: row_num 1 is the first row in the range, column_num 1 is the first column, and so on. The reference returned by INDEX is the intersection of row_num and column_num.
  • If you set row_num or column_num to 0 (zero), INDEX returns the reference for the entire column or row, respectively.
  • row_num, column_num, and area_num must point to a cell within reference; otherwise, INDEX returns a #REF! error. If row_num and column_num are omitted, INDEX returns the area in reference specified by area_num.
  • The result of the INDEX function is a reference and is interpreted as such by other formulas. Depending on the formula, the return value of INDEX may be used as a reference or as a value. For example, the formula CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). The CELL function uses the return value of INDEX as a cell reference. On the other hand, a formula such as 2*INDEX(A1:B2,1,2) translates the return value of INDEX into the number in cell B1.

✦ Tips

  • If both the row_num and column_num arguments are used, INDEX returns the value in the cell at the intersection of row_num and column_num.
  • If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula. Note If you have a current version of Microsoft 365, then you can input the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, input the formula in the top-left-cell of the output range, then press CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.
  • After reference and area_num have selected a particular range, row_num and column_num select a particular cell: row_num 1 is the first row in the range, column_num 1 is the first column, and so on. The reference returned by INDEX is the intersection of row_num and column_num.
  • If you set row_num or column_num to 0 (zero), INDEX returns the reference for the entire column or row, respectively.
  • The result of the INDEX function is a reference and is interpreted as such by other formulas. Depending on the formula, the return value of INDEX may be used as a reference or as a value. For example, the formula CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). The CELL function uses the return value of INDEX as a cell reference. On the other hand, a formula such as 2*INDEX(A1:B2,1,2) translates the return value of INDEX into the number in cell B1.

⚠ Common Errors

#REF! row_num and column_num must point to a cell within array; otherwise, INDEX returns a #REF! error. row_num, column_num, and area_num must point to a cell within reference; otherwise, INDEX returns a #REF! error.
Fix: Check the numbers and the range size
#VALUE! If you specify areas that are not on the same sheet as each other, it will cause a #VALUE! error.
Fix: همه ناحیه‌ها را در یک برگ قرار دهید یا از شکل آرایه‌ای INDEX به همراه تابع CHOOSE برای ترکیب محدوده‌ها استفاده کنید.

⇄ Related Functions

=MATCH() How to use the MATCH function in Excel to search for a specified item in a range of cells, returning the relative position of that item in the range. =XMATCH() The XMATCH function searches for a specified item in an array or range of cells, and then returns the relative position of the first match. =OFFSET() Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned. =XLOOKUP() Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.

💬 Comments & Q&A 0 approved

🔒 Your comment will be published after review.

Share your thoughts — visible after admin approval

No comments yet — be the first!

✍️ Add comment / question

captcha
🔒 Your comment will be published after review.